MySQL – How to Outer Join Multiple Tables to Retrieve Uncommon Records

MySQLmysql-5.5

I'm trying to do an outer join in MySQL to identify records not common to 3 tables. Here's my current effort http://sqlfiddle.com/#!2/02b92/30

I'm trying to retrieve the records not common to all three tables: 2,C,c (tbl2) 6,E,c (tbl3). Instead, I'm getting back the one common record: 1,A,c.

Thanks

SELECT *
FROM tbl1 A
LEFT OUTER JOIN tbl2 B
ON(
  A.a = B.a 
  AND A.b = B.b
  AND A.c = B.c
)
RIGHT OUTER JOIN tbl3 C
ON(
  B.a = C.a 
  AND B.b = C.b
  AND B.c = C.c
)
WHERE A.a is not null;

Best Answer

If you're not dead set upon using OUTER JOIN a way to get the expected result is

SELECT a, b, c
FROM   (SELECT 'tbl1' name, a, b, c
        FROM   tbl1
        UNION ALL
        SELECT 'tbl2' name, a, b, c
        FROM   tbl2
        UNION ALL
        SELECT 'tbl3' name, a, b, c
        FROM   tbl2) d
GROUP BY a, b, c
HAVING COUNT(DISTINCT name) < 3

otherwise you need to use FULL OUTER JOIN a type of join not supported by MySQL, but it can be emulated with a composition like (Full Outer Join in MySQL)

SELECT * FROM tbl1
LEFT JOIN tbl2 ON t1.id = t2.id
UNION
SELECT * FROM tbl1
RIGHT JOIN tbl2 ON t1.id = t2.id
Related Question