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 |
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