SQL JOIN with merging columns with same name-CodePudding

I have two tables, T1 and T2. Both have the columns 'ID', 'X' and 'Y'. In addition, T1 has another column named 'Z' and T2 has another column named 'A'. 'ID' is the primary key. 'X' is directly calculated from 'ID' so if 'ID' matches, also 'X' matches. The column 'Y' exists in both tables but the 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 would like to write an SQL statement that queries 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

Small additional quest if the upper problem is solved: I am using SQLite, which does not support RIGHT JOIN or FULL JOIN.

I tried to do part of the job with:

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 I got:

ID	ID
1	Null
2	2
NULL	3

and many variations.

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