DAY 17 W3 · 需求变绕了：子查询、CTE 与章法高频

IN、EXISTS 与一场生产事故预警

同行群里的事故通报：有人用 NOT IN 子查询查「没下过单的用户」，上线后返回空集，被运营当成故障投诉。你要彻底搞懂，别踩同一个坑。

学 45 min

练 60 min

盘 15 min

共 120 分钟

完成这一天

学 · 45 min

01相关子查询 vs 非相关子查询的执行方式
场景D16 留的尾巴，今天掰开：两种子查询在引擎眼里是两种东西。
非相关子查询（不引用外层列）算一次当常量，优化器还常把它改写成 hash semi-join，效率不差。相关子查询（引用了外层列）逻辑上每行执行一次--但现代优化器对 EXISTS / IN 也常能改写成 join，别靠猜，靠 EXPLAIN。看计划里是 Hash Semi Join（已改写）还是 SubPlan（真在逐行执行）。
```
-- 非相关：算一次
select * from users where city = '上海' and id in (select user_id from orders);
-- 相关：EXPLAIN 看它被改写没有
select * from users u
where exists (select 1 from orders o where o.user_id = u.id and o.status = 2);
```
易错「IN 慢、EXISTS 快」这句老话在 PG 上早已不一定成立--优化器面前两者常常殊途同归，面试要按版本讲。
02EXISTS 的短路特性：找到一行就返回
场景查「下过单的用户」：其实只需要为每个用户找到一个证据。
exists (子查询) 只关心「有没有行返回」：扫到第一行立即返回 TRUE，剩下的全不看。所以子查询里 select 1 还是 select * 完全无所谓--列表根本不会被取值。NOT EXISTS 同理短路。
```
select u.name
from users u
where exists (select 1
              from orders o
              where o.user_id = u.id);
```
易错exists 里别写聚合（子查询的意义是「存在性」，不是算数）；select 1 是惯例，为了读的人一眼看出「这查的是存在性」。
03NOT IN + NULL = 空集的完整推导
场景事故通报的还原：NOT IN 查「没下过单的用户」，上线返回空集，运营以为系统挂了。
一步步推：x NOT IN (a, b, c) 等价于 x<>a AND x<>b AND x<>c。集合里混进一个 NULL，则 x <> NULL 得 UNKNOWN；UNKNOWN 参与 AND，整个表达式永远到不了 TRUE。WHERE 只放行 TRUE --整张表一行都过不了。这不是 bug，是三值逻辑的必然结论（D3 的伏笔今天收了）。
```
select 2 not in (1, 3, null);   -- 结果是 NULL，不是 true！
select count(*) from users
where id not in (select user_id from orders);   -- user_id 有脏 NULL 时 = 0 行
```
易错子查询那一列可能含 NULL（脏数据、外连接结果），就永远别用 NOT IN--用 NOT EXISTS。
04NOT EXISTS 与 LEFT JOIN ... IS NULL 两种反连接写法
场景「没下过单的用户」已经有两把枪了，加上 NOT EXISTS 是三把--该常备哪把？
三者在正确性上等价：D10 的 LEFT JOIN + IS NULL、今天的 NOT EXISTS、以及有 NULL 陷阱的 NOT IN。性能上现代优化器常给三者生成相同计划；语义安全上 NOT EXISTS 没有 NULL 陷阱、也不要求右表连接列非空。默认写 NOT EXISTS，另两种能看懂能改写。
```
select u.name
from users u
where not exists (select 1 from orders o where o.user_id = u.id);

-- 等价的 LEFT JOIN 版（D10）
select u.name
from users u
left join orders o on o.user_id = u.id
where o.id is null;
```
易错面试讲这道题的标准动作：先说「NOT IN 有 NULL 陷阱」，再现场推导，最后给出两种安全写法--一气呵成。

练 · 60 min

用 IN、EXISTS、JOIN 三种写法查「下过单的用户」，对比 EXPLAIN

参考答案

三者结果一致。对比点：EXPLAIN 里 IN / EXISTS 大概率都被优化器改写成 Hash Semi Join（半连接只判存在、不去重），JOIN 版是 Hash Join 之后靠 distinct 补救。「IN 慢 EXISTS 快」的老话在 PG 16 上早已不一定成立。

-- IN 版
select u.name from users u
where u.id in (select user_id from orders);

-- EXISTS 版
select u.name from users u
where exists (select 1 from orders o where o.user_id = u.id);

-- JOIN 版（必须 distinct，否则下过多单的用户重复出现）
select distinct u.name
from users u
join orders o on o.user_id = u.id;

在子查询列里制造 NULL，复现 NOT IN 返回空集
参考答案

seed 里 orders.user_id 没有脏 NULL，所以要自己 union 一行 null 制造事故现场。推导：x NOT IN (a, b, NULL) = x<>a AND x<>b AND x<>NULL，最后一项是 UNKNOWN，AND 链永远到不了 TRUE，WHERE 只放行 TRUE，整表一行都过不了。可对照 select 2 not in (1, 3, null) 的结果是 NULL。
```
select count(*) from users
where id not in (
  select user_id from orders
  union all
  select null::uuid          -- 人为往集合里塞一个 NULL
);
-- 返回 0 行：集合含 NULL，NOT IN 永远算不出 TRUE
```
把第 2 题改成 NOT EXISTS，验证结果正确
参考答案

返回真正没下过单的用户（量级几十人，1 万用户被 5 万单随机覆盖，总有漏网的）；与第 2 题的 0 行形成对比--NOT EXISTS 没有 NULL 陷阱，这是它成为反连接默认写法的原因。
```
select u.name
from users u
where not exists (select 1 from orders o where o.user_id = u.id);
```
再用 LEFT JOIN ... WHERE b.id IS NULL 写第三遍
参考答案

结果与 NOT EXISTS 完全一致。判空列要选右表「本来就不可能为 NULL」的列（主键 o.id 最稳）：连接失败的行整个右表全 NULL，判空才可靠。D10 的老朋友，今天多了个对照物。
```
select u.name
from users u
left join orders o on o.user_id = u.id
where o.id is null;
```
在 12 万行明细上跑三种反连接写法，做一张耗时对比表

参考答案

方法：\timing on（或 explain analyze）把 NOT EXISTS / LEFT JOIN...IS NULL / NOT IN 三条各跑 3 次取中位数，记进对比表。预期：这个量级下三者耗时同一量级，计划里能看到 Hash Anti Join--现代优化器常把三种写法殊途同归。结论行写上：性能差不多时按语义安全选，默认 NOT EXISTS。

过关标准 手上有三写法耗时对比表；能完整讲出 NOT IN 遇 NULL 的推导过程。

学 · 45 min

01相关子查询 vs 非相关子查询的执行方式

02EXISTS 的短路特性：找到一行就返回

03NOT IN + NULL = 空集的完整推导

04NOT EXISTS 与 LEFT JOIN ... IS NULL 两种反连接写法

练 · 60 min

出题端点设置