Ben Ben - 3 months ago 6
SQL Question

How to not lose records in full join

Let's say I have two tables; table A and table shown below:

A
Color ID
Blue 1
Green 2
Red 3

B
Color ID
Blue 1
Brown 2
Red 3


If I were to attempt to join them using a full join, the result would depend on which table I use in the select statement. For example the following query would produce the following result

select A.color, count(*)
from A
full join B on a.color = B.color
group by 1
order by 1

color count
Blue 1
Green 1
Red 1
1


If I decided to use B.color in the select statement instead of A.color, I would get the result below:

color count
Blue 1
Brown 1
Red 1
1


How would I get the resultset to include all values for color. I know I could accomplish using unionall, and I could use a case statement in the select statement to use one when the other is null, but is there another cleaner way to accomplish this?

Thanks,
Ben

vkp vkp
Answer

Use coalesce to pick up the value from the other table in case the value exists in one table and not the other.

select coalesce(A.color,B.color) as color, count(*)
from A 
full join B on a.color = B.color
group by 1
order by 1