开发者

SQL Double-Outer Join?

开发者 https://www.devze.com 2022-12-08 23:17 出处:网络
I have two tables A and B...A and B can be joined by a common column.A and B will have some rows that match on the join column.But A has some rows that B doesn\'t have, and B has some rows that A does

I have two tables A and B... A and B can be joined by a common column. A and B will have some rows that match on the join column. But A has some rows that B doesn't have, and B has some rows that A doesn't have.

A LEFT OUTER JOIN from A to B will give 开发者_如何学运维all rows in A, filling in NULLS for the B columns that couldn't be matched. A RIGHT OUTER JOIN gives all rows in B, filling in NULLS for the A columns that couldn't be matched.

I would like to receive ALL rows from both A and B, while returning NULL for the A columns on rows that are only in B, and NULL for the B columns that are only in A.

I'm know I can do this with UNION along with 1 INNER + 1 LEFT + 1 RIGHT, but is there a single SELECT query that can achieve this?


You can use a FULL OUTER JOIN for this.


You want a full outer join. Don't try doing this with UNIONS -- there are some very tricky edge cases, and it is almost impossible to get right.

Syntax is here:

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


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

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号