Troyen Troyen - 13 days ago 5
SQL Question

Why am I getting blank values on the matched column for a FULL OUTER JOIN?

I have two tables I'm trying to combine together. For simplicity's sake, they have two columns each: an ID that is common across the tables, and a numerical value that is distinct. However, not all IDs are present in both tables, hence my use of a FULL OUTER JOIN.

Table1 Table2
+------+--------+ +------+--------+
| ID | Value1 | | ID | Value2 |
+------+--------+ +------+--------+
| ABC | 125 | | ABC | 317 |
| DEF | 13 | | HIJ | 95 |
+------+--------+ +------+--------+


I was hoping the OUTER JOIN would give me all records like so:

+------+--------+--------+
| ID | Value1 | Value2 |
+------+--------+--------+
| ABC | 125 | 317 |
| DEF | 13 | |
| HIJ | | 95 |
+------+--------+--------+


But what I'm seeing instead is the ID is not copied over to the merged result if it doesn't exist in Table1:

+------+--------+--------+
| ID | Value1 | Value2 |
+------+--------+--------+
| ABC | 125 | 317 |
| DEF | 13 | |
| | | 95 |
+------+--------+--------+


Why would my ID be blank? My query is as follows:

SELECT ID, Value1, Value2
FROM Table1
FULL OUTER JOIN Table2 ON Table1.ID == Table2.ID;


I'm using a company-internal language closest to SQL Server. While there a few differences, I thought I would check my understanding of join syntax first.

Dai Dai
Answer

Try this:

SELECT
    COALESCE( Table1.ID, Table2.ID ) AS [ID],
    Table1.Value1,
    Table2.Value2
FROM
    Table1
    FULL OUTER JOIN Table2 ON Table1.ID = Table2.ID
Comments