'SQL outer join on two columns, returning null in one column if only the other matches
I couldn't find exactly what I'm looking for in another thread.
Let's say I have these two tables:
left:
| x | left_y |
|---|---|
| a | 1 |
| a | 2 |
| b | 4 |
| b | 5 |
right:
| x | right_y |
|---|---|
| a | 2 |
| a | 3 |
| b | 5 |
| b | 6 |
I want to run a query close to this in intention:
SELECT *
FROM left FULL OUTER JOIN right
ON (left.x = right.x AND left.left_y = right.right_y)
OR left.x = right.x
And get an output that has no nulls in x, but maybe has a null in left_y or right_y
| x | left_y | right_y |
|---|---|---|
| a | 1 | null |
| a | 2 | 2 |
| a | null | 3 |
| b | 4 | null |
| b | 5 | 5 |
| b | null | 6 |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
