[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 574
  • Last Modified:

sql server 2012 - union table

hi experts
i am reading about union table
ANSI SQL-89
Tables joined by commas in FROM Clause
SELECT ...
FROM   Table1, Table2
WHERE  <where_predicate>

but i don understand: Not recommended: accidental Cartesian products!

can explain me with an example, like hands on lab
0
enrique_aeo
Asked:
enrique_aeo
1 Solution
 
lwadwellCommented:
Have a look at this example:
IF OBJECT_ID('tempdb..#table1') IS NOT NULL
	DROP TABLE #table1;
CREATE TABLE #table1 (
    id  int,
    val varchar(20)
);
insert into #table1 values (1,'table1 row1'),(2,'table1 row2'),(3,'table1 row3');
IF OBJECT_ID('tempdb..#table2') IS NOT NULL
	DROP TABLE #table2;
CREATE TABLE #table2 (
    id  int,
    val varchar(20)
);
insert into #table2 values (1,'table2 row1'),(2,'table2 row2');

-- INNER JOIN Syntax
SELECT *
  FROM #table1 t1
 INNER JOIN #table2 t2 ON t1.id = t2.id;

-- JOIN using comma in FROM, specify column join in WHERE clause
-- equivalent to INNER JOIN above.
SELECT *
  FROM #table1 t1, #table2 t2
 WHERE t1.id = t2.id;

-- FORCED Cartesian Product i.e. CROSS JOIN
-- note: no 'ON' clause
SELECT *
  FROM #table1 t1
 CROSS JOIN #table2 t2;

-- Accidental Cartesian Product using comma in FROM
-- note: no WHERE clause 
--       ... or more likely, forgot to specify column join in a larger WHERE clause
SELECT *
  FROM #table1 t1, #table2 t2;

-- Accidental Cartesian Product using INNER JOIN
-- note: syntax error
SELECT *
  FROM #table1 t1
 INNER JOIN #table2 t2;

Open in new window

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now