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 |
>Solution :
You can use coalesce:
select coalesce(l.x, r.x) as x,
left_y,
right_y
from l full outer join r
on l.x = r.x
and l.left_y = r.right_y