sqlite - Full Outer Join in sqlite on 4 tables

Question

我需要根据公共(public)主键连接 4 个表。如果 sqlite 实现了完全外部连接，它可能看起来像这样(不考虑优化)。

SELECT S.pair, C.ball, P.bluejeans, B.checkered 
FROM Socks S 
FULL OUTER JOIN Caps C 
FULL OUTER JOIN Pants P 
FULL OUTER JOIN Boxers B 
WHERE S.color = C.color AND S.color = P.color AND S.color = B.color;

我仔细研究了很长时间，我发现最好的是这个 2 表 sqlite 全连接，它是用左连接和 union alls 实现的:

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;

我正在尝试修改它以适用于 2 个以上的表，但我是 SQL 的新手，而且我不会太过分。是否有可能在合理的时间内得到这个结果？

我认为我对 3 个表有一个正确的实现(它可能不正确)，但我似乎仍然无法为 4 个表得到它。这是我对 3 个表的实现:

SELECT S.pair, C.ball, P.bluejeans 
FROM Socks S LEFT JOIN Caps C LEFT JOIN Pants P 
ON C.color = S.color AND P.color = S.color 
UNION ALL 
SELECT S.pair, C.ball, P.bluejeans 
FROM Socks S LEFT JOIN Caps C LEFT JOIN Pants P 
ON S.color = C.color AND S.color = P.color 
WHERE S.color IS NULL;

非常感谢任何帮助

Answer 1

SQLite 中两个表 A 和 B 之间的完全外部连接的一般构造确实是:

SELECT ... FROM A LEFT JOIN B ON ...
UNION ALL
SELECT ... FROM B LEFT JOIN A ON ... WHERE A.key IS NULL

现在create a view SocksCaps 用于 Socks 和 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

对 Pants 和 Boxers 做同样的事情。

然后像对待表一样对待这些 View ，并使用相同的构造进行完全外部连接:

SELECT ... FROM SocksCaps LEFT JOIN PantsBoxers ON ...
UNION ALL
SELECT ... FROM PantsBoxers LEFT JOIN SocksCaps ON ... WHERE SocksCaps.color IS NULL