1:N 关联表上的「排除」查询，LEFT JOIN + WHERE != 会悄悄漏数据

某天收到一个反馈：「按标签过滤的列表里，明明勾选了「不含某标签」，但结果里还是看到了带这个标签的记录。」第一反应是前端传参出错，日志一查参数完全正确——问题出在 SQL 本身。

这个坑几乎人人踩过，而且因为大多数记录只有一个标签，小数据量下根本不会触发，等数据量上来才开始「随机」出现。

现象

主表（客户/订单/用户……）和关联标签表是 1:N 关系。需求是「查询不含标签 X 的主记录」。

写出来的 SQL 是这样的：

-- 错误写法：返回了「有其他标签」的记录，即便它同时拥有标签 X
SELECT c.*
FROM main_entity c
LEFT JOIN entity_tag t ON t.entity_id = c.id
WHERE t.tag_id != 'X';

肉眼看起来逻辑没问题，但结果「漏了」——那些同时有标签 X 和标签 Y 的实体，因为 (entity, Y) 这一行满足 != 'X'，照样出现在列表里。

根因

JOIN 是行级操作。LEFT JOIN entity_tag 会把一条主记录展开成若干行（每个标签一行）。WHERE t.tag_id != 'X' 只能把 tag_id = X 的那一行过滤掉，但同一个主记录的其他标签行依然把它「带回来」了。

换句话说：WHERE 谓词过滤的是行，不是父实体。负向谓词（!=、NOT IN）作用在展开后的每一行上，无法表达「父实体在整个集合里没有这个值」。

坑：1:N JOIN 后的 WHERE != 只过滤行，不过滤父实体

父实体有 N 条关联行，!= X 只能排掉其中 tag = X 的那一行；其他兄弟行会把父实体重新带回结果集。实体有多少个其他标签，结果里就重复出现多少次。

三种等效的错误写法：

LEFT JOIN ... WHERE t.tag_id != 'X'
INNER JOIN ... WHERE t.tag_id != 'X'
JOIN ... WHERE t.tag_id NOT IN ('X')

根本原因相同：过滤视角是「单行」，不是「父实体的完整标签集」。

正解

先用子查询找出「拥有标签 X」的父实体集合，再用 NOT IN / NOT EXISTS 从主表里排除它。

-- 正确：NOT IN 子查询
SELECT c.*
FROM main_entity c
WHERE c.id NOT IN (
    SELECT t.entity_id
    FROM entity_tag t
    WHERE t.tag_id IN ('X')   -- 内层用正向 IN
);

记忆口诀：正向 IN 写在子查询里，NOT IN 包在外层。绝不把否定谓词直接写进 JOIN 后面的 WHERE。

NOT EXISTS 是等价替代，在关联列可能为 NULL 时更安全（NOT IN 遇到 NULL 值会让整个结果变空）：

-- 正确：NOT EXISTS（nullable 列首选）
SELECT c.*
FROM main_entity c
WHERE NOT EXISTS (
    SELECT 1
    FROM entity_tag t
    WHERE t.entity_id = c.id
      AND t.tag_id IN ('X')
);

MySQL 8 的优化器对这两种写法都能转化为 antijoin，执行计划等价。

ORM 写法（ThinkPHP 5/8）：

Db::name('main_entity')
    ->whereNotIn('id', function ($q) use ($tagId) {
        $q->name('entity_tag')
          ->where('tag_id', 'in', [$tagId])
          ->field('entity_id');
    })->select();

正解：子查询反转——先圈出「有 X 的」再整体排除

核心逻辑：NOT IN (SELECT key FROM relation WHERE relation.col IN (X)) 或 NOT EXISTS。

子查询的 IN 命中索引，是 O(log n) 的点查；外层 NOT IN 做 antijoin，不产生行乘积，结果天然去重。

上线前自测清单

造一条主记录，给它同时打上「被排除的标签 X」和「另一个标签 Y」。
查询「不含标签 X」，确认这条记录不出现在结果里。
EXPLAIN 检查：子查询应通过 ref 或 eq_ref 命中索引，而非 != 的全扫描。

一句话外卖

在 1:N 关系表上做「排除」，永远用子查询把父实体集合先框出来再排除，而不是在 JOIN 后面加负向 WHERE。