使用 SQLite 进行全外连接

Question

SQLite 只有 INNER 和 LEFT JOIN。

有没有办法用 SQLite 进行 FULL OUTER JOIN？

原文由 Yada 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

是的，请参阅 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

原文由 Mark Byers 发布，翻译遵循 CC BY-SA 2.5 许可协议

Answer 2

从 SQLite 3.39.0 开始原生支持 FULL OUTER JOIN：

2.1。输入数据的确定（FROM子句处理）

“FULL JOIN”或“FULL OUTER JOIN”是“LEFT JOIN”和“RIGHT JOIN”的组合。为左侧数据集中不匹配右侧行的每一行以及右侧数据集中不匹配左侧行的每一行添加额外的输出行。不匹配的列用 NULL 填充。

---

演示：

 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;

询问：

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

db<>小提琴演示

原文由 Lukasz Szozda 发布，翻译遵循 CC BY-SA 4.0 许可协议