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
