SQL Full Outer Join W / Coalesce
Why does this query create 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 OUT JOIN TABLE_3
ON Table1.ID=Table3.ID;
Result:
1
2
3
4
4
The request duplicates all values, where T1 is zero and T2 / T3 has the same value. Duplicates are removed for any other combination.
source to share
It's a little difficult to explain. If you show other IDs, you will see the full range of events:
"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 end up with a single line because t1 and t2 create a line with t2.id = 4
and t1.id = null
when they don't match. Then you get the same when t3.id = 4
. Comparison - t1.id
- so you get another row. There is no comparison with t2.id
.
I suspect you intend more logically:
select coalesce(t1.id, t2.id, t3.id)
from t1 full join
t2
using (id) full join
t3
using (id);
Here is the SQL script.
source to share