子查询结果很小时优先用in，因其可转为哈希查找、内存开销低；结果很大时用exists，依赖短路机制和索引点查；务必补全关联条件并用explain验证执行计划，注意null对in/not in的语义影响。
子查询结果很小，比如几十行，直接用 IN
当子查询返回的是静态值或极小结果集（如 SELECT id FROM dept WHERE name IN ('技术部', '产品部')），IN 更轻量：数据库优化器常将其转为哈希查找，避免逐行调用子查询的开销。此时 EXISTS 反而多出相关子查询的解析和执行成本。
常见错误现象：IN (SELECT ...) 却套在大数据主表上，子查询只返回 3 行，但主表有 50 万行——这时仍用 IN 没问题；但如果误以为“只要子查询小就一定快”，没注意主表是否能走索引，也可能慢。
- 适用场景：枚举类筛选（部门、状态码）、配置表关联、预计算 ID 列表
- 必须确保子查询不含
NULL，否则整条IN表达式结果为UNKNOWN，WHERE 条件不成立，查不到数据 - MySQL 8.0+ 对
IN (subquery)有物化优化，但遇到ORDER BY或LIMIT会退化为全量执行
主表小、子查询表大且有索引，优先用 EXISTS
典型场景是“查有订单的用户”：users 表 1 万行，orders 表 200 万行，orders.user_id 有索引。这时 EXISTS 能利用索引快速定位每条用户的匹配记录，找到即停；而 IN 得先把 200 万 user_id 全拉出来再做匹配，内存和 IO 压力陡增。
性能差异真实存在：某生鲜平台实测，“有订单的商品”查询从 IN 的 4.8 秒降到 EXISTS 的 0.9 秒——关键不是语法高级，而是执行路径不同。
-
EXISTS子查询里写SELECT 1、SELECT NULL或SELECT *都一样，数据库只判是否存在行 - 必须保证子查询中关联字段（如
o.user_id = u.id）一侧有索引，否则EXISTS也会变全表扫描 - 不要写成
EXISTS (SELECT * FROM t WHERE ...)然后在子查询里加复杂聚合——这破坏短路逻辑，等价于强制全扫
NOT IN 几乎总是错的，一律改用 NOT EXISTS
NOT IN 遇到子查询任意一行是 NULL，整个条件变成 UNKNOWN，结果集直接为空。这不是 bug，是 SQL 三值逻辑的必然表现。哪怕你加了 WHERE col IS NOT NULL，也不能 100% 规避（比如子查询来自 JOIN，中间表字段为 NULL）。
而 NOT EXISTS 完全不关心 NULL：它只问“能不能找到匹配行”，没找到就是 TRUE，逻辑干净。
- 错误写法：
WHERE id NOT IN (SELECT user_id FROM orders)——只要orders.user_id有一条 NULL，结果永远为空 - 正确等价写法：
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id) -
NOT IN无法利用索引，NOT EXISTS可以，尤其当子查询字段有索引时
不确定时，先看执行计划，别猜
所谓“主表小/大”“子表小/大”，不是看总行数，而是看实际参与扫描的行数。比如主表加了 WHERE status = 'active' 后只剩 100 行，那它就是“小主表”，适合 EXISTS；反之，如果子查询加了 LIMIT 10 却没被优化器识别，仍可能全扫。
真正可靠的判断依据只有 EXPLAIN 输出：
- 看到
type=ALL或rows远超预期 → 当前写法可能低效 -
EXISTS对应的子查询显示Using index→ 索引生效 -
IN对应的子查询显示Using temporary; Using filesort→ 结果集太大，该换写法了
复杂点在于：同一语句在 MySQL 和 PostgreSQL 中的执行策略可能不同，EXISTS 在 Oracle 里还涉及谓词推入细节——不能只背结论，得看自己库的实际计划。
