SQL - join multiple tables, with one little catch

Question

My apologies in advance if this particular question has already been asked and answered ... there so many different particular ways the JOIN command is used that it can be difficult to find the exact answer to a given problem. I'm a SQL novice, so ... if the solution exists, feel free to point me to it.

I'm trying to join 3 different tables, and I believe what I want is equivalent to a FULL OUTER JOIN (not supported by MySQL, as I understand) on all 3 tables. Consider a Venn diagram with 3 circles; I want the full union of all 3 circles, including the full intersection, all three pair-wise joins (where one table returns NULLs), and all three single instances (where two tables return NULLs). I believe what I've got here will work, but it's brute-force and I'm sure there is a more efficient way. I'm also a bit concerned with my use of WHERE NOT EXISTS, so please correct me if necessary. Here's the gist of my code:

    // Intersection of all three tables
    SELECT [table1.cols], [table2.cols], [table3.cols]
        FROM table1
            INNER JOIN table2
                ON table1.col1 = table2.col1
            INNER JOIN table3
                ON table1.col1 = table3.col1

    UNION ALL

    // Intersection of tables one and two
    SELECT [table1.cols], [table2.cols], [NULLS]
        FROM table1
            INNER JOIN table2
                ON table1.col1 = table2.col1
                    WHERE NOT EXISTS (table1.col1 = table3.col1)

    UNION ALL

    // Intersection of tables two and three
    SELECT [NULLS], [table2.cols], [table3.cols]
        FROM table2
            INNER JOIN table3
                ON table2.col1 = table3.col1
                    WHERE NOT EXISTS (table2.col1 = table1.col1)

    UNION ALL

    // Intersection of tables three and one
    SELECT [table1.cols], [NULLS], [table3.cols]
        FROM table3
            INNER JOIN table1
                ON table3.col1 = table1.col1
                    WHERE NOT EXISTS (table3.col1 = table2.col1)

    UNION ALL

    // Only in table one
    SELECT [table1.cols], [NULLS], [NULLS]
        FROM table1
            WHERE NOT EXISTS ((table1.col1 = table2.col1))
            AND NOT EXISTS ((table1.col1 = table3.col1))

    UNION ALL

    // Only in table two
    SELECT [NULLS], [table2.cols], [NULLS]
        FROM table2
            WHERE NOT EXISTS ((table2.col1 = table1.col1))
            AND NOT EXISTS ((table2.col1 = table3.col1))

    UNION ALL

    // Only in table three
    SELECT [NULLS], [NULLS], [table3.cols]
        FROM table3
            WHERE (NOT EXISTS (table3.col1 = table1.col1))
            AND (NOT EXISTS (table3.col1 = table2.col1))

TIA for your help, and your grace. :)

I guess I should have noted ... the "one little catch", which seems to make this different from most questions about joining multiple tables, is that I want to keep all the data from all three tables in the joined table. — Grant M.
– Grant M., Commented Feb 24, 2012 at 19:06
Take a look at the solution here: stackoverflow.com/questions/2384298/… — sazh
– sazh, Commented Feb 24, 2012 at 19:13
So, from Cletus's answer for three tables: Are you sure this won't result in duplicate date in the joined table? — Grant M.
– Grant M., Commented Feb 24, 2012 at 19:20
UNION will remove duplicate data, UNION ALL will keep duplicates — sazh
– sazh, Commented Feb 24, 2012 at 19:35
@GrantM., did you ever get it resolved? or is it still pending... — DRapp
– DRapp, Commented Feb 24, 2012 at 20:38

DRapp · Accepted Answer · 2012-02-24 20:46:01Z

1

to simulate your full outer join, I would pre-query just the UNIQUE IDs you are EXPECTING, then LEFT JOIN to each other...

select
      AllPossibleKeys.CommonID,
      T1a.*,
      T2a.*,
      T3a.*
   from
      ( select distinct T1.col1 as CommonID
           from table1 T1
        UNION
        select distinct T2.col1 as CommonID
           from table2 T2
        UNION
        select distinct T3.col1 as CommonID
           from table1 T3 ) as AllPossibleKeys

      LEFT JOIN table1 T1a
         on AllPossibleKeys.CommonID = T1a.col1

      LEFT JOIN table2 T2a
         on AllPossibleKeys.CommonID = T2a.col1

      LEFT JOIN table3 T3a
         on AllPossibleKeys.CommonID = T3a.col1

answered Feb 24, 2012 at 20:46

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - join multiple tables, with one little catch

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related