找回密码
立即注册
搜索
发回帖 发新帖

6854

积分

1

好友

858

主题
发表于 11 小时前 | 查看: 2| 回复: 0

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:

IN 子查询返回 id1 为 1 和 2 的结果

但如果不小心把子查询中的 id2 写成 id1:

SELECT id1
FROM test1
WHERE id1 IN (SELECT id1 FROM test2);

结果可能变成 1、2、3:

字段名写错后查询返回 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);

结果为空:

NOT IN 子查询包含 NULL 时返回空结果

原本期望得到 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




上一篇:MySQL 高危漏洞 CVE-2026-114952:受影响版本与修复
下一篇:Intel NPU 5架构拆解:NCE从6个减到3个,面积为何能缩小36.9%?
您需要登录后才可以回帖 登录 | 立即注册

关闭

站长推荐上一条 /1 下一条

手机版|小黑屋|网站地图|云栈社区 ( 苏ICP备2022046150号-2 )

GMT+8, 2026-11-21 16:03 , Processed in 0.071468 second(s), 40 queries , Gzip On.

Powered by Discuz! X3.5

© 2025-2026 云栈社区.

快速回复 返回顶部 返回列表