sql server 2012 - union table

Posted on 2012-09-11
Last Modified: 2012-09-11
hi experts
i am reading about union table
Tables joined by commas in FROM Clause
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
Question by:enrique_aeo
    1 Comment
    LVL 25

    Accepted Solution

    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 =;
    -- JOIN using comma in FROM, specify column join in WHERE clause
    -- equivalent to INNER JOIN above.
    SELECT *
      FROM #table1 t1, #table2 t2
     WHERE =;
    -- 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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now