SQL Full Outer Join W/ Coalesce

Question

Why does this query produce duplicates in some scenarios?

Table_1   
ID   
1  
2  
3  

Table_2  
ID  
1  
2  
4  

Table_3  
ID  
1  
3  
4  

Query:

SELECT COALESCE(Table_1.ID, Table_2.ID, Table_3.ID)  
FROM Table_1

FULL OUTER JOIN TABLE_2  
ON Table1.ID=Table_2.ID  

FULL OUTER JOIN TABLE_3  
ON Table1.ID=Table3.ID;

Result:

1  
2  
3  
4  
4  

The query duplicates all values where T1 is null and T2/T3 share the same value. Duplicates are removed for any other combination.

Answer 1

This is a little bit hard to explain. If you show the other ids, you will see the full range of what happens:

"coalesce"  "id1"   "id2"   "id3"
    1         1      1        1
    2         2      2        .
    3         3      .        3
    4         .      4        .
    4         .      .        4

You can see the results here.

So, You get one row because t1 & t2 create a row with t2.id = 4 and t1.id = null when they don't match. Then, you get the same thing when t3.id = 4. The comparison is to t1.id -- so you get another row. There is no comparison to t2.id.

I suspect that you intend logic more like this:

select coalesce(t1.id, t2.id, t3.id)
from t1 full join
     t2
     using (id) full join
     t3
     using (id);

Here is the SQL Fiddle.