user5032790 user5032790 - 4 months ago 10
SQL Question

Select non-null column in FULL OUTER JOIN

Consider the following tables:

Id | Name | Family
1 | name1 | family1
2 | name2 | family2


And:

Id | Orderr | Countt
1 | order1 | 17
1 | order2 | 18
3 | order3 | 16


And the following query:

select table1.id,table1.name,table1.family,table2.orderr,table2.countt
from table1 FULL OUTER JOIN table2
on table1.id = table2.id


It returns:

Id | Name | Family | Orderr | Countt
1 | name1 | family1 | order1 | 17
1 | name1 | family1 | order2 | 18
2 | name2 | family2 | NULL | NULL
NULL | NULL | NULL | order3 | 16


As you see in the last row it doesn't show the Id column. How can I change my query to return the Id column in the last row? I don't want to include
table2.id
in my select query because in this way I will have tow Id column.

Answer

Use COALESCE or ISNULL

COALESCE "returns the first non-null expression among its arguments," and ISNULL "replaces NULL with the specified replacement value."

Analysis in details at http://sqlmag.com/t-sql/coalesce-vs-isnull

SELECT ISNULL(table1.id, table2.id) AS id,
table1.name,table1.family,table2.orderr,table2.countt
FROM table1 FULL OUTER JOIN table2
ON table1.id = table2.id

OR

SELECT COALESCE(table1.id, table2.id) AS id,
table1.name,table1.family,table2.orderr,table2.countt
FROM table1 FULL OUTER JOIN table2
ON table1.id = table2.id