databasesqlitejoinorganization

SQLITE: Keep all unmatched rows during join

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?

Solution

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