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
Full Outer Join in sqlite on 4 tables
The general construction for a full outer join between two tables A
and B
in SQLite indeed is:
SELECT ... FROM A LEFT JOIN B ON ...
UNION ALL
SELECT ... FROM B LEFT JOIN A ON ... WHERE A.key IS NULL
Now create a view SocksCaps
for the full outer join between Socks
and Caps
:
CREATE VIEW SocksCaps AS
SELECT ... FROM Socks LEFT JOIN Caps ON ...
UNION ALL
SELECT ... FROM Caps LEFT JOIN Socks ON ... WHERE Socks.color IS NULL
Do the same for Pants
and Boxers
.
Then treat these views just like tables and do a full outer join with the same construction:
SELECT ... FROM SocksCaps LEFT JOIN PantsBoxers ON ...
UNION ALL
SELECT ... FROM PantsBoxers LEFT JOIN SocksCaps ON ... WHERE SocksCaps.color IS NULL
SQLite: full outer join with four tables
Assuming that your sample tables has data like below
Table1:
a b lon lat
---------------
22 33 11 22
Table2:
c d lon lat
---------------
1 2 44 45
Table3
e f lon lat
-----------------------
NULL NULL 100 101
Table4
g h lon lat
-----------------------
NULL NULL 200 201
and you want to merge the records, you can use union all
.
select a,b,NULL as c, NULL as d,NULL as e, NULL as f, NULL as g, NULL as h, lon,lat
from table1
union all
select NULL, NULL,c,d,NULL as e, NULL as f, NULL as g, NULL as h, lon,lat
from table2
union all
select NULL, NULL,NULL,NULL,e,f, NULL as g, NULL as h, lon,lat
from table3
union all
select NULL, NULL,NULL,NULL,NULL,NULL,g,h, lon,lat
from table4
Result:
+------+------+------+------+------+------+------+------+-----+-----+
| a | b | c | d | e | f | g | h | lon | lat |
+------+------+------+------+------+------+------+------+-----+-----+
| 22 | 33 | NULL | NULL | NULL | NULL | NULL | NULL | 11 | 22 |
| NULL | NULL | 1 | 2 | NULL | NULL | NULL | NULL | 44 | 45 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 100 | 101 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 200 | 201 |
+------+------+------+------+------+------+------+------+-----+-----+
DEMO
SQLite: full outer join with four tables with 30+ columns
I don't believe there's a particularly neat way of doing this when you have 30+ columns. The following is the best I could do, using nested CTEs to implement full outer joins, then using coalesce
to choose the first non-null lat/lon.
It's still required to enumerate all 30+ fields in the top SELECT
statement, but at least the massive lists of NULL AS ...
aren't needed:
SELECT
a, b, c, d, e, f, g, h,
coalesce(lat1, lat2, lat3, lat4) AS lat,
coalesce(lon1, lon2, lon3, lon4) AS lon
FROM (
WITH t1_x_t2 AS (
SELECT t1.*, t2.*,
t1.lat AS lat1, t2.lat AS lat2, t1.lon AS lon1, t2.lon AS lon2
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON 0
UNION ALL
SELECT t1.*, t2.*,
t1.lat AS lat1, t2.lat AS lat2, t1.lon AS lon1, t2.lon AS lon2
FROM table2 t2 LEFT OUTER JOIN table1 t1 ON 0
), t3_x_t4 AS (
SELECT t3.*, t4.*,
t3.lat AS lat3, t4.lat AS lat4, t3.lon AS lon3, t4.lon AS lon4
FROM table3 t3 LEFT OUTER JOIN table4 t4 ON 0
UNION ALL
SELECT t3.*, t4.*,
t3.lat AS lat3, t4.lat AS lat4, t3.lon AS lon3, t4.lon AS lon4
FROM table4 t4 LEFT OUTER JOIN table3 t3 ON 0
)
SELECT t1_x_t2.*, t3_x_t4.* FROM t1_x_t2 LEFT OUTER JOIN t3_x_t4 ON 0
UNION ALL
SELECT t1_x_t2.*, t3_x_t4.* FROM t3_x_t4 LEFT OUTER JOIN t1_x_t2 ON 0
)
SQLite3 Simulate RIGHT OUTER JOIN with LEFT OUTER JOIN's without being able to change table order
Ignoring column order, x right join y on c
is y left join x on c
. This is commonly explicitly said. (But you can also just apply the definitions of the joins to your original expression to get subexpressions with the values you want.) You can read the from
grammar to see how you can parenthesize or subquery for precedence. Applying the identity we get ts left join (cs left join (p left join cp on x) on y) on z
.
Similarly, ignoring column order, x full join y on c
is y full join x on c
. Expressing full join
in terms of left join
& right join
is a frequently asked duplicate.
Related Topics
SQL - How to Store and Navigate Hierarchies
How to Find Gaps in Sequential Numbering in MySQL
How to Force Postgres to Use a Particular Index
What Are Covering Indexes and Covered Queries in SQL Server
Dynamic SQL - Exec(@Sql) Versus Exec Sp_Executesql(@Sql)
Generate SQL Create Scripts For Existing Tables With Query
Ms Access Query: Concatenating Rows Through a Query
Simple Query to Grab Max Value For Each Id
What's the Best Way to Join on the Same Table Twice
Unknown Column in 'Field List' Error on MySQL Update Query
Group by to Combine/Concat a Column
How to Do a Case Sensitive Search in Where Clause (I'M Using SQL Server)
Filter Table Before Applying Left Join
Return Multiple Columns of the Same Row as Json Array of Objects