Nighthawk: Created page with "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 F..."

2019-05-16T18:23:30Z

Created page with "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 F..."

New page

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/ http://www.sqlitetutorial.net/sqlite-full-outer-join/]

sqlite FULL JOIN emulation - Revision history

Nighthawk: Created page with "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 F..."