SQL Full Outer Join Three Tables
December 6th, 2011 | Published in Programming, Web Development
Here is some code to do a full outer join on three tables.
In the example, the three full outer joins are done on two columns , but it works just the same if you only have a single join column.
CREATE TABLE ZA (T CHAR(1), O CHAR(1)) CREATE TABLE ZB (T CHAR(1), O CHAR(1)) CREATE TABLE ZC (T CHAR(1), O CHAR(1)) INSERT INTO ZA (T, O) VALUES ('1', 'A') INSERT INTO ZA (T, O) VALUES ('2', 'B') INSERT INTO ZA (T, O) VALUES ('3', 'C') INSERT INTO ZA (T, O) VALUES ('4', 'D') INSERT INTO ZB (T, O) VALUES ('1', 'A') INSERT INTO ZB (T, O) VALUES ('2', 'B') INSERT INTO ZB (T, O) VALUES ('5', 'E') INSERT INTO ZB (T, O) VALUES ('6', 'F') INSERT INTO ZC (T, O) VALUES ('2', 'B') INSERT INTO ZC (T, O) VALUES ('3', 'C') INSERT INTO ZC (T, O) VALUES ('4', 'D') INSERT INTO ZC (T, O) VALUES ('5', 'E') INSERT INTO ZC (T, O) VALUES ('7', 'G') SELECT ISNULL(ISNULL(A.T, B.T), C.T), ISNULL(ISNULL(A.O, B.O), C.O) FROM ZA A FULL OUTER JOIN ZB B ON A.T = B.T AND A.O = B.O FULL OUTER JOIN ZC C ON ISNULL(A.T, B.T) = C.T AND ISNULL(A.O, B.O) = C.O DROP TABLE ZA DROP TABLE ZB DROP TABLE ZC