SQL Double-Outer Join?

Question

I have two tables A and B ... A and B can be joined by a common column. A and B will have multiple rows that correspond to the join column. But A has several lines that B does not have, and B has some lines that do not have A.

A LEFT OUTER JOIN from A to B will produce all rows in A, filling in NULLS for columns of B that cannot be matched. RIGHT OUTER JOIN exposes all rows in B, filling in NULLS for columns A that cannot be matched.

I would like to get ALL rows from A and B, returning NULL for columns A in rows that are only in B, and NULL for columns B that are only in A.

I know I can do this with UNION along with 1 INNER + 1 LEFT + 1 RIGHT, but is there one SELECT query that can do this?

Answer 1

You can use FULL OUTER JOIN for this.

Answer 2

You want a full outer join. Do not try to do this with UNIONS - there are some very complex cases of cross and it is almost impossible to get right.

The syntax is here:

http://msdn.microsoft.com/en-us/library/aa213228(SQL.80).aspx

Answer 3

Yes, LEFT ... UNION ... RIGHT is the same as FULL OUTER JOIN.