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., Feb 24 '12 at 19:06
Take a look at the solution here: http://stackoverflow.com/questions/2384298/mysql-full-outer-join-syntax-error — sazh, Feb 24 '12 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., Feb 24 '12 at 19:20
UNION will remove duplicate data, UNION ALL will keep duplicates — sazh, Feb 24 '12 at 19:35
@GrantM., did you ever get it resolved? or is it still pending... — DRapp, Feb 24 '12 at 20:38
Cletus's solution (from diaho) looks like it will work, but I won't know until I'm able to try it (once I get home). I'll update status later this evening. — Grant M., Feb 24 '12 at 20:49

score 1 · Answer 1 · answered Feb 24 '12 at 20:46

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

SQL - join multiple tables, with one little catch

1 Answers1