如何利用SQL实现全外连接效果_MySQL环境下的Union与Join组合-SQL-PHP中文网

MySQL不支持FULL OUTER JOIN，需用LEFT JOIN、RIGHT JOIN与UNION（ALL）组合实现：先取左连全集，再补右表独有行并显式填充NULL，UNION ALL保真高效，配合NOT EXISTS避免NULL陷阱。

如何利用sql实现全外连接效果_mysql环境下的union与join组合

FULL OUTER JOIN 在 MySQL 中根本不存在——不是语法不熟，是它压根没被实现。你敲 FULL OUTER JOIN，MySQL 会直接报错 ERROR 1064。但业务上真需要“两边数据全要、不匹配就补 NULL”，就得靠 LEFT JOIN、RIGHT JOIN 和 UNION（或 UNION ALL）自己搭。

用 `UNION` 拼出全外连接：去重但可能丢数据

最常见写法是把左连和右连结果合并：

SELECT e.id, e.name, d.id AS dept_id, d.name AS dept_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.id, e.name, d.id AS dept_id, d.name AS dept_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

这个写法看似合理，但有两处硬伤：

UNION 会自动去重，如果某条记录在左右两个子查询里都出现（比如完全匹配的行），会被合并成一条——这本身没问题；但若原始表中存在重复数据（如多个同名员工、或同一部门被多次插入），这些重复行会被意外抹掉
WHERE e.id IS NULL 这个过滤条件只加在右连部分，是为了避免交集重复；但逻辑上它依赖 e.id 是主键或非空字段，一旦 e.id 允许为 NULL，这部分就会漏数据
性能上，UNION 需要排序 + 去重，对大表很慢

用 `UNION ALL` + 显式排除交集：保真且高效

真正可靠的做法是分三块拼：LEFT JOIN 的全部结果 + RIGHT JOIN 中“左表无匹配”的那部分，用 UNION ALL 合并：

SELECT e.id, e.name, d.id AS dept_id, d.name AS dept_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION ALL
SELECT NULL AS id, NULL AS name, d.id AS dept_id, d.name AS dept_name
FROM departments d
WHERE d.id NOT IN (
  SELECT department_id FROM employees WHERE department_id IS NOT NULL
);

或者更稳妥地用 NOT EXISTS 替代 NOT IN（避免 NULL 导致整个条件失效）：

第一段 LEFT JOIN 拿到所有员工 + 匹配部门（含 department_id 为 NULL 的员工）
第二段单独查“有部门但没员工”的记录，显式构造 NULL 填充左表字段
UNION ALL 不去重、不排序，速度比 UNION 快得多，且保留原始重复行
字段别名和 NULL 类型需对齐，否则 MySQL 会报 ERROR 1222

连接条件含多字段时，`ON` 子句必须严格一致

比如按 account 和 init_date 两个字段关联两张流水表，LEFT JOIN 和后续筛选里的条件必须完全一样：

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

LEFT JOIN data_stock2 b ON a.account = b.account AND a.init_date = b.init_date

如果第二段用 WHERE NOT EXISTS (SELECT 1 FROM data_stock2 b WHERE b.account = a.account) 就错了——漏了日期维度。容易踩的坑包括：

在 RIGHT JOIN 或 NOT EXISTS 子查询里只判一个字段，导致本该补 NULL 的行被漏掉
忽略 NULL 在 = 判断中的特殊行为，例如 a.field = b.field 永远不成立当任一为 NULL，所以必须用 IS NULL 或 IS NOT NULL 显式处理
字段类型不一致（如 VARCHAR 和 CHAR 带空格），导致看似相等实则不匹配

复杂场景下优先考虑临时表或 CTE（MySQL 8.0+）

当涉及三张以上表、或中间结果需复用时，硬塞在一个 UNION 里会极难维护。MySQL 8.0 支持 CTE，可读性提升明显：

WITH left_part AS (
  SELECT e.*, d.name AS dept_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id
),
right_only AS (
  SELECT NULL AS id, NULL AS name, d.id AS dept_id, d.name AS dept_name
  FROM departments d
  WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id)
)
SELECT * FROM left_part UNION ALL SELECT * FROM right_only;

但要注意：