LEFT JOIN + IS NULL 如何实现 NOT IN 的替代写法

LEFT JOIN + IS NULL 能替代 NOT IN 是因为后者在子查询含 NULL 时返回空集，而前者通过显式保留左表所有行并筛选右表无匹配记录，行为确定可靠；需注意 ON 条件排除右表 NULL、WHERE 判右表字段、复杂条件须移入 ON。

LEFT JOIN + IS NULL 为什么能替代 NOT IN

因为 NOT IN 在子查询结果含 NULL 时会整体返回空集，逻辑上完全失效；而 LEFT JOIN 显式保留左表所有行，再用 IS NULL 筛出右表无匹配的记录，行为确定、可预测。

典型错误现象：select * FROM a WHERE id NOT IN (SELECT id FROM b) —— 只要 b.id 里有一个 NULL，整条语句查不到任何数据，且不报错、不易察觉。

标准写法与关键细节

把 NOT IN (SELECT x FROM t2) 改成：

SELECT a.*  FROM a  LEFT JOIN b ON a.id = b.id  WHERE b.id IS NULL

必须注意以下几点：

• ON 条件里右表字段不能为 NULL（否则 LEFT JOIN 仍会产生匹配行），推荐用右表的主键或非空唯一列（如 b.id）
• WHERE 中判断的是右表的字段是否为 NULL，不是左表字段
• 若右表有重复值，LEFT JOIN 会产生多行，但 IS NULL 筛选后结果仍等价于 NOT IN 的语义（去重逻辑由业务决定，sql 层不隐式去重）
• 索引建议：确保 b.id 有索引，否则 JOIN 性能可能比子查询更差

遇到 NULL 值时的兼容处理

如果右表连接字段本身允许 NULL（比如 b.ref_id 是外键且可空），直接 ON a.id = b.ref_id 会导致 NULL 被当成“不匹配”，从而错误地进入 IS NULL 结果集。此时应：

• 在 ON 条件中排除右表 NULL：ON a.id = b.ref_id AND b.ref_id IS NOT NULL
• 或改用 NOT EXISTS（语义更清晰，且天然规避 NULL 陷阱）

示例（安全写法）：

SELECT a.*  FROM a  LEFT JOIN b ON a.id = b.ref_id AND b.ref_id IS NOT NULL  WHERE b.ref_id IS NULL

性能和可读性取舍要点

LEFT JOIN + IS NULL 在大数据量下通常比 NOT IN 子查询更快，尤其当右表有合适索引时；但可读性略低，容易误写成 WHERE a.id IS NULL 这类逻辑错误。

真正容易被忽略的是：如果原 NOT IN 子查询带了复杂条件（如 WHERE status = 'active'），这些条件必须挪到 LEFT JOIN 的 ON 子句里，而不是 WHERE —— 否则会把本该保留的左表行过滤掉。

例如，NOT IN (SELECT id FROM b WHERE deleted = 0) 必须写成：LEFT JOIN b ON a.id = b.id AND b.deleted = 0，而非 LEFT JOIN b ON a.id = b.id WHERE b.deleted = 0。