Difference between revisions of "sqlite FULL JOIN emulation"

Latest revision as of 18:23, 16 May 2019

Emulating SQLite full outer join The following statement emulates the FULL OUTER JOIN clause in SQLite:

SELECT d.type,
        d.color,
        c.type,
        c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
        d.color,
        c.type,
        c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;
How the query works.

Because SQLilte does not support the RIGHT JOIN clause, we use the LEFT JOIN clause in the second SELECT statement instead and switch the positions of the cats and dogs tables. The UNION ALL clause retains the duplicate rows from the result sets of both queries. The WHERE clause in the second SELECT statement removes rows that already included in the result set of the first SELECT statement.

taken from http://www.sqlitetutorial.net/sqlite-full-outer-join/