SQL outer join on two columns, returning null in one column if only the other matches

Question

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

Answer 1

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

Fiddle