How to JOIN on columns with same name?-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

Assuming there is another table t3 with:

ID	X	Y	B
3	X3	Y5	B1
4	X4	Y6	B2

Is there an easier way to create the record

ID	X	T1Y	T2Y	T3Y	Z	B	A
1	X1	Y1	NULL	NULL	Z1	NULL	NULL
2	X2	Y2	Y3	NULL	Z2	NULL	A1
3	X3	NULL	Y4	Y5	NULL	B1	A2
4	X4	NULL	NULL	Y6	NULL	B2	NULL

than this:

with tt as
(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)
select tt.id, tt.x, tt.t1y, tt.t2y, t3.y t3y, tt.z, t3.b, tt.a
from tt
left join t3 on tt.id = t3.id
union all
select t3.id, t3.x, tt.t1y, tt.t2y, t3.y, tt.z, t3.b, tt.a
from t3
left join tt on tt.id = t3.id
where tt.id is null