Full Outer Join

[Guest]

How do I do a full outer join on two tables? Or do I have to do a left outer
join unioned to an exception join...


Jim Thomlinson

 

[Ken Snell [MVP]]

A full outer join is done by unioning a left outer join with a right outer
join:

SELECT A.*, B.*
FROM TableA AS A LEFT JOIN
TableB AS B ON A.FieldName = B.FieldName
WHERE B.FieldName Is Null
UNION
SELECT C.*, D.*
FROM TableA AS C RIGHT JOIN
TableB AS D ON C.FieldName = D.FieldName
WHERE C.FieldName Is Null;

 

[Ken Snell [MVP]]

Actually, a slight "misspeak" in my post... your comment about doing a left
join unioned with a right join that selects only unmatched items is correct.
Usng the UNION ALL makes the query run a bit faster than just using UNION
(the latter must eliminate duplicates, and this query won't pull
duplicates).


SELECT A.*, B.*
FROM TableA AS A LEFT JOIN
TableB AS B ON A.FieldName = B.FieldName
UNION ALL
SELECT C.*, D.*
FROM TableA AS C RIGHT JOIN
TableB AS D ON C.FieldName = D.FieldName
WHERE C.FieldName Is Null;

 

[Guest]

Kinda what I thought... but your confirmation is helpful...
--
HTH...

Jim Thomlinson

 

[ooxx]

wow Ken Snell,

I like this. It's strange!

Thanks,