FULL OUTER JOIN with SQLite
SQLite only has INNER and LEF开发者_开发技巧T JOIN.
Is there a way to do a FULL OUTER JOIN with SQLite?
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
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.
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
For people, searching for an answer to emulate a Distinct Full Outer Join: Due to the fact, that SQLite does neither support a Full Outer Join, nor a Right Join, i had to emulate a distinct full outer join / an inverted inner join (however you might call it). The following Venn diagram shows the expected output:
To receive this expected output, i combined two Left Join clauses (the example refers to two identical built tables with partially differing data. I wanted to output only the data which does either appear in table A OR in table B).
SELECT A.flightNumber, A.offblockTime, A.airspaceCount, A.departure, A.arrival FROM D2flights A
LEFT JOIN D1flights B
ON A.flightNumber = B.flightNumber
WHERE B.flightNumber IS NULL
UNION
SELECT A.flightNumber, A.offblockTime, A.airspaceCount, A.departure, A.arrival FROM D1flights A
LEFT JOIN D2flights B
ON A.flightNumber = B.flightNumber
WHERE B.flightNumber IS NULL
The SQLite statement above returns the expected result in one query. It appears, that the UNION clause does also order the output via the flightNumber column.
The code has been tested with SQLite version 3.32.2
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.
精彩评论