怎样在SQL中使用FULL OUTER JOIN合并两表差异数据-SQL-PHP中文网

mysql不支持full outer join，会报error 1064；需用left join + right join（加where过滤交集）+ union all组合模拟，注意字段对齐、null显式声明及not in中null陷阱。

怎样在sql中使用full outer join合并两表差异数据

MySQL不支持FULL OUTER JOIN，得用LEFT + RIGHT模拟

直接写 FULL OUTER JOIN 在 MySQL 里会报错 ERROR 1064——它压根没实现这个语法。PostgreSQL、SQL Server、Oracle 支持，但 MySQL 用户必须手动拼：先 LEFT JOIN 拿左表全量 + 右表匹配行，再 RIGHT JOIN 补右表独有行，最后用 UNION ALL 合并（注意不是 UNION，避免去重开销）。

常见错误是漏掉 WHERE t2.id IS NULL 条件，导致 RIGHT JOIN 部分重复左表已存在的行。

LEFT JOIN 部分：保留所有左表记录，右表无匹配则字段为 NULL
RIGHT JOIN 部分：只取右表有、左表无的记录（加 WHERE t1.id IS NULL）
两部分字段顺序、数量、类型必须一致，否则 UNION ALL 失败

PostgreSQL中FULL OUTER JOIN的NULL判断要小心

FULL OUTER JOIN 后，左右表都可能产生 NULL 值，但它们的语义不同：左表 NULL 表示“右表有这行，左表没匹配”，右表 NULL 表示“左表有这行，右表没匹配”。实际查差异时，不能只看某一边是否为 NULL，得组合判断。

比如想查“仅在表A存在”的数据，条件得写成：t1.id IS NOT NULL AND t2.id IS NULL；反过来查“仅在表B存在”就是 t1.id IS NULL AND t2.id IS NOT NULL。

别写成 t2.id IS NULL 单独判断——它会把两表都不匹配的脏数据也捞出来（虽然少见，但JOIN条件松散时可能发生）
如果JOIN字段允许 NULL，IS NULL 判断会误判；建议用非空主键或添加 COALESCE(t1.id, -1) != COALESCE(t2.id, -1) 辅助校验

性能差？因为FULL OUTER JOIN无法用索引优化右表驱动部分

LEFT JOIN 能走左表主键 + 右表外键索引，但 FULL OUTER JOIN（或模拟写法中的 RIGHT JOIN 部分）往往触发右表全扫描——尤其当右表大、左表小时，WHERE t1.id IS NULL 条件无法利用左表索引反向过滤。

实测过一个 500 万行右表 + 10 万行左表的场景，模拟 FULL OUTER JOIN 耗时 8.2 秒，而改用子查询预筛右表再 JOIN 降到 1.3 秒。

优先考虑是否真需要全量合并：有时用 UNION ALL 拼两个 SELECT（分别查各自独有数据）更稳
给 JOIN 字段建联合索引，如 (id, updated_at)，避免回表放大 I/O
PostgreSQL 可加 /*+ Leading(t2) */ 提示（需开启扩展），但效果依赖统计信息准确度

用UNION ALL模拟时，列别名和NULL占位必须显式声明

模拟写法里，LEFT 和 RIGHT 两部分 SELECT 的列数、类型、顺序必须严格一致，否则 UNION ALL 直接报错。很多人卡在右边部分少写一个 NULL AS col_name，或者类型不匹配（比如左边是 VARCHAR(50)，右边写 NULL 默认成 TEXT）。

示例：合并用户表和订单表，按 user_id 关联，但订单表可能缺用户信息：

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION ALL
SELECT NULL, NULL, o.order_id, o.amount
FROM orders o
WHERE o.user_id NOT IN (SELECT id FROM users WHERE id IS NOT NULL);