How to JOIN on columns with same name?

Question

I have tables T1 and T2. Both have columns ID, X and Y. In addition, T1 has another column Z and T2 has another column A. ID is primary key. X is calculated from ID (so if ID matches, also X matches). Column Y exists in both tables but content is not the same relative to ID. A and Z do not have anything to do with each other.

T1:

ID	X	Y	Z
1	X1	Y1	Z1
2	X2	Y2	Z2

T2:

ID	X	Y	A
2	X2	Y3	A1
3	X3	Y4	A2

I want a query which returns a record that contains all data from both tables and fills in NULL whenever a field has no data.

Result:

ID	X	T1Y	T2Y	Z	A
1	X1	Y1	NULL	Z1	NULL
2	X2	Y2	Y3	Z2	A1
3	X3	NULL	Y4	NULL	A2

My SQLite version does not support RIGHT JOIN or FULL JOIN. I tried :

SELECT T1.ID, T2.ID
FROM   T1 
       LEFT JOIN T2
          ON T1.ID = T2.ID
UNION
SELECT T1.ID, T2.ID
FROM   T2 
       LEFT JOIN T1 
          ON T1.ID = T2.ID
WHERE  T1.ID IS NULL
ORDER BY T1.ID

But got:

ID	ID
1	Null
2	2
NULL	3

score 2 · Answer 1 · answered Nov 09 '22 at 20:25

2

Since version 3.39.0 SQLite supports FULL OUTER JOIN:

SELECT COALESCE(T1.ID, T2.ID) ID,
       COALESCE(T1.X, T2.X) X,
       T1.Y T1Y,
       T2.Y T2Y,
       T1.Z,
       T2.A
FROM T1 FULL OUTER JOIN T2
ON T2.ID = T1.ID;

See the demo.

answered Nov 09 '22 at 20:25

forpas

160,666
10
38
76

Obviously this works, thanks! Unfortunately a lot of tools and APIs are still stuck at older versions of SQLite. – JoKing Nov 10 '22 at 14:32

score 1 · Accepted Answer · answered Nov 09 '22 at 20:16

We can phrase the full join with union all like so:

select t1.id, t1.x, t1.y t1y, t2.y t2y, t1.z, t2.a
from t1
left join t2 on t1.id = t2.id
union all
select t2.id, t2.x, t1.y, t2.y, t1.z, t2.a
from t2
left join t1 on t1.id = t2.id
where t1.id is null

Demo on DB Fiddle

How to JOIN on columns with same name?

2 Answers2