IN 和 NOT IN 是 SQL 中常用的关键字,可以用于判断一个值是否属于某个集合。但在使用子查询时,查询性能、字段名解析和 NULL 值都值得留意。
为什么要留意 IN 和 NOT IN?
1. 查询性能可能有差异
项目中曾遇到这样一个情况:t1 和 t2 表各有约 150 万条数据,大小都在 600 MB 左右。执行下面的查询时,耗时达到十几分钟:
SELECT *
FROM t1
WHERE phone NOT IN (SELECT phone FROM t2);
检查后发现,两个表的 phone 字段都建了索引,字段类型也相同。将查询改成 NOT EXISTS 后,耗时降到了约 20 秒:
SELECT *
FROM t1
WHERE NOT EXISTS (
SELECT phone
FROM t2
WHERE t1.phone = t2.phone
);
这是一次具体场景中的表现,并不意味着 NOT IN 一定无法使用索引,也不能说明 NOT EXISTS 总是更快。实际性能取决于数据库版本、执行计划、数据分布和索引等因素,应结合执行计划与实际耗时判断。
另外,两种写法在涉及 NULL 时不一定等价。替换前需要确认字段是否允许 NULL,以及业务期望如何处理这些值。
2. 字段名写错时,子查询可能不报错
先创建两张表 test1 和 test2,并插入测试数据:
CREATE TABLE test1 (id1 INT);
CREATE TABLE test2 (id2 INT);
INSERT INTO test1 (id1) VALUES (1), (2), (3);
INSERT INTO test2 (id2) VALUES (1), (2);
查询 test1 中也存在于 test2 的 ID,可以写成:
SELECT id1
FROM test1
WHERE id1 IN (SELECT id2 FROM test2);
查询结果为 1 和 2:

但如果不小心把子查询中的 id2 写成 id1:
SELECT id1
FROM test1
WHERE id1 IN (SELECT id1 FROM test2);
结果可能变成 1、2、3:

为什么没有报错?单独执行 SELECT id1 FROM test2 时,由于 test2 没有 id1 字段,会报列名无效的错误。但在这个子查询中,未限定表名的 id1 可能被解析为外层 test1.id1,从而形成相关子查询。由于 test2 非空,每个非空的外层 id1 都能匹配自身,于是三行都被返回。
这不是 IN 独有的问题:编写多表查询和子查询时,给字段加上表名或别名,有助于避免类似错误。
3. NOT IN 遇到 NULL 可能返回空结果
接下来向 test2 插入一个 NULL:
INSERT INTO test2 (id2) VALUES (NULL);
假设业务需要查询 test1 中不存在于 test2 的 ID,执行:
SELECT id1
FROM test1
WHERE id1 NOT IN (SELECT id2 FROM test2);
结果为空:

原本期望得到 3,为什么没有返回?
SQL 使用三值逻辑。与 NULL 的比较结果通常是“未知”,而不是普通的真或假。对于 3 NOT IN (1, 2, NULL),虽然 3 <> 1 和 3 <> 2 为真,但 3 <> NULL 的结果是未知,因此整个条件不能确定为真,WHERE 不会保留这一行。
建表时应根据业务决定字段是否允许 NULL;对于不允许为空的字段,可以使用 NOT NULL 约束。若保留 NOT IN,也可以在符合业务语义的前提下过滤子查询中的 NULL,同时确认外层字段为 NULL 时应如何处理。
可以怎么改写?
1. 使用 EXISTS 或 NOT EXISTS
使用 EXISTS 查询存在匹配项的记录,使用 NOT EXISTS 查询不存在匹配项的记录:
SELECT *
FROM test1
WHERE EXISTS (
SELECT *
FROM test2
WHERE test2.id2 = test1.id1
);
SELECT *
FROM test1
WHERE NOT EXISTS (
SELECT *
FROM test2
WHERE test2.id2 = test1.id1
);
在前面的测试数据中,第二条查询会返回 id1 为 3 的记录。需要注意,外层字段为 NULL 时,NOT EXISTS 和 NOT IN 的结果也可能不同。
2. 使用 JOIN
也可以使用 JOIN 完成匹配查询,或使用 LEFT JOIN 查找没有匹配项的记录:
SELECT test1.id1
FROM test1
INNER JOIN test2 ON test2.id2 = test1.id1;
SELECT test1.id1
FROM test1
LEFT JOIN test2 ON test2.id2 = test1.id1
WHERE test2.id2 IS NULL;
需要留意结果是否等价:当 test2 中存在多个相同的匹配值时,INNER JOIN 可能重复返回 test1 的记录,而 IN 和 EXISTS 不会因为匹配项重复而增加外层结果行。LEFT JOIN 查找未匹配记录时,也应选择合适的字段判断 NULL,并确认外层 NULL 的处理符合业务需求。
什么时候可以使用 IN?
并不是说 IN 和 NOT IN 一律不能用。对于确定且有限的集合,例如 IN (0, 1, 2),直接使用 IN 往往清晰易读。
涉及子查询时,重点是检查字段引用、NULL 语义和实际执行计划,再决定是否改写为 EXISTS 或 JOIN。
来源
cnblogs.com/hydor/p/5391556.html