FULL OUTER JOIN with SQLite

Following Jonathan Leffler's comment in Mark Byers' answer, here's an alternative answer which uses UNION instead of UNION ALL:

SELECT * FROM table_name_1 LEFT OUTER JOIN table_name_2 ON id_1 = id_2
UNION
SELECT * FROM table_name_2 LEFT OUTER JOIN table_name_1 ON id_1 = id_2

Edit: The original source for the SQLite example above and from where further SQLite examples could be found was http://sqlite.awardspace.info/syntax/sqlitepg06.htm but it seems as though that site is now returning a 404 Not Found error.


Yes, see the example on Wikipedia.

SELECT employee.*, department.*
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.*, department.*
FROM   department
       LEFT JOIN employee
          ON employee.DepartmentID = department.DepartmentID
WHERE  employee.DepartmentID IS NULL

FULL OUTER JOIN is natively supported starting from SQLite 3.39.0:

2.1. Determination of input data (FROM clause processing)

enter image description here

A "FULL JOIN" or "FULL OUTER JOIN" is a combination of a "LEFT JOIN" and a "RIGHT JOIN". Extra rows of output are added for each row in left dataset that matches no rows in the right, and for each row in the right dataset that matches no rows in the left. Unmatched columns are filled in with NULL.

Demo:

CREATE TABLE t1 AS
SELECT 1 AS id, 'A' AS col UNION
SELECT 2 AS id, 'B' AS col;

CREATE TABLE t2 AS
SELECT 1 AS id, 999 AS val UNION
SELECT 3 AS id, 100 AS val;

Query:

SELECT *
FROM t1
FULL JOIN t2
  ON t1.id = t2.id;

db<>fiddle demo

Tags:

Sql

Sqlite

Join

Full Outer Join

Recent Posts