I'm trying to join multiple tables.
Table 1:
| ID | TEMP | DESC | NUMB |
|---|---|---|---|
| 32 | 89 | Y | 6 |
| 47 | NULL | Y | 5 |
| 56 | 43 | N | 4 |
| 34 | 54 | N | 3 |
| 22 | 78 | NULL | NULL |
Table 2
| ID | IND | FLAV |
|---|---|---|
| 32 | Y | G |
| 47 | N | G |
| 56 | Y | R |
| 34 | Y | B |
| 22 | Y | Y |
Table 3:
| ID | COLOR | SHAPE |
|---|---|---|
| 32 | RED | SQUARE |
| 47 | BLUE | CIRCLE |
| 52 | NULL | TRI |
| 22 | ORANGE | NULL |
I want the resulting table:
| ID | TEMP | DESC | NUMB | IND | FLAV | COLOR | SHAPE |
|---|---|---|---|---|---|---|---|
| 32 | 89 | Y | 6 | Y | G | RED | SQUARE |
| 47 | NULL | Y | 5 | N | G | BLUE | CIRCLE |
| 56 | 43 | N | 4 | Y | R | NULL | NULL |
| 34 | 54 | N | 3 | Y | B | NULL | NULL |
| 22 | 78 | NULL | NULL | Y | Y | ORANGE | NULL |
| 52 | NULL | NULL | NULL | NULL | NULL | NULL | TRI |
The row order of the resulting ID's doesn't matter to me. I've tried:
SELECT *
FROM Table1
INNER JOIN Table2 USING(ID)
LEFT JOIN Table3 USING(ID)
But it leaves out ID 52. I want to be sure no unmatched ID's from either table are left out.
Is this possible in SQLITE?
CodePudding user response：
For this requirement the correct type of join is FULL OUTER JOIN wich is not supported by SQLite.
A workaround is to use a subquery that returns the distinct ids of all 3 tables and then do LEFT joins to the tables with the USING clause:
SELECT *
FROM (SELECT ID FROM Table1 UNION SELECT ID FROM Table2 UNION SELECT ID FROM Table3) t
LEFT JOIN Table1 USING (id)
LEFT JOIN Table2 USING (id)
LEFT JOIN Table3 USING (id);
See the demo.