FULL OUTER JOIN with SQLite

Question

SQLite only has INNER and LEFT JOIN.

Is there a way to do a FULL OUTER JOIN with SQLite?

Answer 1

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 

Answer 2

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.

Answer 3

FULL OUTER JOIN is natively supported starting from SQLite 3.39.0:

2.1. Determination of input data (FROM clause processing)

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

Answer 4

I will belatedly pitch in my 2 cents. Consider the 2 simple tables people1 and people2 below:

   id   name age
0   1    teo  59
1   2   niko  57
2   3  maria  54 


    id   name weight
0   1    teo    186
1   2  maria    125
2   3    evi    108

First, we create a temporaty view, v_all, where we join with UNION the two opposite LEFT JOINS as below:

CREATE TEMP VIEW v_all AS
              SELECT p1.name AS name1, p1.age,
                    p2.name AS name2, p2.weight
              FROM people1 p1
              LEFT JOIN people2 AS p2 
              USING (name)
              UNION
              SELECT p1.name AS name1, p1.age,
                  p2.name AS name2, p2.weight
              FROM people2 AS p2
              LEFT JOIN people1 AS p1
              USING (name);

However, we end up with 2 name columns,name1 and name2, which may have a null value or equal values. What we want is to combine name1 and name2 in a single column name. We can do that with a CASE query as below:

SELECT age,weight,
                CASE
                  WHEN name1 IS NULL
                    THEN name2
                  WHEN name2 IS NULL
                    THEN name1
                  WHEN name1=name2
                    THEN name1
                END name
              FROM v_all

And we finally end up with:

      name weight   age
0    evi    108  None
1  maria    125    54
2   niko   None    57
3    teo    186    59

Of course you could combine the two in a single query, without having to create a temp view. I avoided doing so, in order to highlight the insufficiency of just 2 left joins and a union, which is what i have seen so far recommended.