一、三种子查询，对应三套运算符

按返回结果的行数分：

单行子查询返回多行，Oracle 直接报 ORA-01427。 多行子查询配 =，Oracle 报 ORA-01427 或 ORA-00913。 这些错都是好事——它们在提醒你「你以为只有一个，其实有多个」。 真正危险的是反过来的情况：SQLite 不报错，悄悄取第一行。 同一个写法在 Oracle 上是错、在 SQLite 上能跑，这才是最容易埋雷的地方。

二、单行子查询：最直观的一种

where sal > (select avg(sal) from emp)——括号里只返回一个数，外层拿它去比。 这是子查询最常见的用法：先算一个基准值，再拿它筛行。 执行顺序是「由内向外」：先跑括号里的，再跑外层。 但这不是绝对的——如果子查询和外层无关，优化器可以只算一次然后复用。 子查询可以出现在三个地方：where 里、select 里、from 里。 from 里的那个叫「内联视图」，本质上就是临时表，第 9 章会细讲。

三、多行子查询：in、any、all

in 是「在这个集合里」，最常用。 > all (...) 是「比里面最大的还大」，> any (...) 是「比里面最小的还大」。 这两个词很容易记反，记法：ALL 是「全都满足」，ANY 是「有一个满足就行」。 > all 要求比每一个都大 → 等价于 > max()；> any 只要比某一个大 → 等价于 > min()。 in 其实是 = any 的简写，not in 是 <> all 的简写。 所以能用 in 的地方就别写 any，可读性好得多。 而且 any／all 不是所有引擎都支持——SQLite 就没有。

四、坑：not in 遇到 NULL 会静默返回空

这是 SQL 里最著名的坑，而且它不报错、不给警告、一行都不返回。 原因：not in 会被展开成一串 and。 x not in (a, b, NULL) 等价于 x <> a and x <> b and x <> NULL。 而 x <> NULL 的结果是「未知」，整串 and 就永远是「未知」。 「未知」不是「真」，所以一行都留不下。 子查询结果里只要有一个 NULL，整条 not in 就报废。 注意是「一个就够」——不是「全都是 NULL 才有问题」。 上面那段 Demo 里，mgr 列只有一个 NULL（KING 是总裁），整条查询就从 8 行变成 0 行。 两种解法： ① 在子查询里加 where 列 is not null；② 改用 not exists。 推荐第二种——它从根本上不做 <> NULL 这种比较， 所以不会踩这个坑，而且语义更清楚：「不存在这样的行」。

五、关联子查询：里外互相引用

子查询里引用了外层的列，就叫关联子查询。 它不能先单独跑——外层每取一行，就得把这一行的值塞进去算一次。 概念上很慢：外层 14 行，子查询就跑 14 次。 优化器通常会把这种写法改写成连接， 但别指望一定改写——尤其是子查询里有聚合、有 rownum、有复杂条件的时候。 exists 是关联子查询的标准搭档。 它只关心「有没有匹配的行」，不关心「匹配的是什么」， 所以子查询里写 select 1 就够了，写 select * 也一样。 它是「找到一行就停」，不是「全部扫完」。 not exists 和 not in 的语义差别，就在 NULL 上。 not in 拿值去比，遇到 NULL 就卡住；not exists 只问「有没有这样的行」，NULL 不影响它。 老手见到 not in 就皱眉、见到 not exists 就放心，原因就在这。

六、动手 Demo：五种写法，其中一个是坑

这一段按「单行 → 多行 → 关联」的顺序走，每一段配一句「它在问什么问题」。 第三段是那个坑：not in 先给 0 行，加上 is not null 之后变成 8 行。 同一个查询意图，两个完全不同的结果。

import sqlite3
c = sqlite3.connect(":memory:")
c.execute("create table emp (empno int, ename text, job text, "
          "mgr int, sal real, deptno int)")
c.executemany("insert into emp values (?,?,?,?,?,?)", [
    (7369,'SMITH','CLERK',7902,800,20),
    (7566,'JONES','MANAGER',7839,2975,20),
    (7782,'CLARK','MANAGER',7839,2450,10),
    (7839,'KING','PRESIDENT',None,5000,10),
    (7902,'FORD','ANALYST',7566,3000,20),
])

# 一、单行子查询：返回一个值
print(c.execute("select ename from emp where sal > "
                 "(select avg(sal) from emp)").fetchall())

# 二、多行子查询：返回一列多值（ALL／ANY 用 max／min 等价写）
print(c.execute("select ename from emp where sal > "
                 "(select max(sal) from emp where deptno = 10)").fetchall())

# 三、not in 遇到 NULL：整条查询静默返回空
print(c.execute("select ename from emp where empno not in "
                 "(select mgr from emp)").fetchall())
print(c.execute("select ename from emp where empno not in "
                 "(select mgr from emp where mgr is not null)").fetchall())

# 四、关联子查询：里外互相引用
print(c.execute("select e.ename from emp e where e.sal > "
                 "(select avg(sal) from emp x where x.deptno = e.deptno)").fetchall())

# 五、exists／not exists：只问「有没有」
print(c.execute("select e.ename from emp e where exists "
                 "(select 1 from emp x where x.mgr = e.empno)").fetchall())
print(c.execute("select e.ename from emp e where not exists "
                 "(select 1 from emp x where x.mgr = e.empno)").fetchall())

实跑输出：

--------------------------------------------------------------------
第十九课：子查询——把一条查询的结果，当成另一条查询的输入
--------------------------------------------------------------------

  **子查询就是「查询里套查询」。**
  **外层叫主查询，括号里那层叫子查询。**
  **子查询先跑，跑出来的结果给外层用。**
  **按返回多少行分三种：单行（一个值）、多行（一列多值）、关联（里外互相引用）。**
  **这个分法不是学术分类，是「你能用哪个运算符」的分级。**


  一、单行子查询：返回一个值
  --------------------------------------------------------------------

  SQL> select ename, sal from emp where sal > (select avg(sal) from emp) order by sal desc
  ENAME SAL
  ----------
  KING  5000
  SCOTT 3000
  FORD  3000
  JONES 2975
  BLAKE 2850
  CLARK 2450
  已选择 6 行
  **括号里那条 `select avg(sal) from emp` 只返回一个数**（就是全公司平均工资），
  **所以外层能直接拿 `>` 去比。**

  SQL> select ename, job, sal from emp where job = (select job from emp where ename = 'SMITH') order by sal
  ENAME  JOB   SAL
  -----------------
  SMITH  CLERK 800
  JAMES  CLERK 950
  ADAMS  CLERK 1100
  MILLER CLERK 1300
  已选择 4 行
  **这条的意思是「找出和 SMITH 同工种的人」。**
  **子查询先查 SMITH 的工种（CLERK），外层再拿 CLERK 去筛。**

  SQL> select ename from emp where sal = (select sal from emp where job = 'CLERK')
  ENAME
  -----
  SMITH
  已选择 1 行
  **小心：上面这条没报错，只返回了 `SMITH` 一行。**
  **因为 SQLite 悄悄取了子查询的第一个值（800），拿它去比。**
  **Oracle 会直接报 `ORA-01427: 单行子查询返回多行`，一个结果都不给你。**
  **这是两套引擎最容易骗人的差别：SQLite 的宽松让你以为语法对了。**
  **「能跑但没意义」的 SQL 比报错更危险。**

  二、多行子查询：返回一列多值
  --------------------------------------------------------------------

  SQL> select ename, job, sal from emp where deptno in (select deptno from dept where loc = 'DALLAS') order by ename
  ENAME JOB     SAL
  ------------------
  ADAMS CLERK   1100
  FORD  ANALYST 3000
  JONES MANAGER 2975
  SCOTT ANALYST 3000
  SMITH CLERK   800
  已选择 5 行

  [Oracle 原码 · 本沙箱不执行]
  | select ename from emp
  | where sal > ALL (select sal from emp where deptno = 10);
  | 
  | select ename from emp
  | where sal > ANY (select sal from emp where deptno = 10);
    SQLite 没有 ALL／ANY 关键字，下面用 max／min 写等价版。


  SQL> select ename, sal from emp where sal > (select max(sal) from emp where deptno = 10) order by sal
  ENAME SAL
  ---------
  已选择 0 行

  SQL> select ename, sal from emp where sal > (select min(sal) from emp where deptno = 10) order by sal
  ENAME  SAL
  -----------
  TURNER 1500
  ALLEN  1600
  CLARK  2450
  BLAKE  2850
  JONES  2975
  SCOTT  3000
  FORD   3000
  KING   5000
  已选择 8 行
  **`> all (...)` 是「比里面最大的还大」，`> any (...)` 是「比里面最小的还大」。**
  **所以这两条用 `max`／`min` 写完全等价**——**ALL／ANY 只是把意思说得更直白，不会更快。**
  **这两个词很容易记反**，记法：**ALL 是「全都满足」，ANY 是「有一个满足就行」。**
  **`in` 其实是 `= any` 的简写**，`not in` 是 `<> all` 的简写。
  **所以能用 `in` 的地方不用写 `any`**，可读性更好。

  三、坑：not in 遇到 NULL 会静默返回空
  --------------------------------------------------------------------

  SQL> select count(*) as mgr里的空值 from emp where mgr is null
  MGR里的空值
  -----------
  1
  已选择 1 行

  SQL> select ename from emp where empno not in (select mgr from emp)
  ENAME
  -----
  已选择 0 行
  **返回 0 行。** 但 `emp` 里明摆着有 14 个人，**至少有一批普通员工不是任何人的上级**，
  结果却一行都没有。

  SQL> select ename from emp where empno not in (select mgr from emp where mgr is not null) order by ename
  ENAME
  ------
  ADAMS
  ALLEN
  JAMES
  MARTIN
  MILLER
  SMITH
  TURNER
  WARD
  已选择 8 行
  **加上 `where mgr is not null` 之后，八行普通员工全回来了。**
  **原因：`not in` 展开成 `empno <> NULL and empno <> 7566 and ...`，而 `empno <> NULL` 的结果是「未知」，整串 `and` 就永远是「未知」，「未知」不是「真」，一行都留不下。**
  **`mgr` 那一列只有一个 NULL（KING 是总裁，没有上级），就这一个 NULL 让整条查询报废。**
  **而且它不报错——这是 SQL 里最著名的坑之一。**
  **规矩：子查询的结果列如果有 NULL，`not in` 一定出问题。要么加 `is not null`，要么改用 `not exists`。**

  四、关联子查询：里外互相引用
  --------------------------------------------------------------------

  SQL> select e.ename, e.sal, e.deptno from emp e where e.sal > (select avg(sal) from emp x where x.deptno = e.deptno) order by e.deptno, e.sal
  ENAME SAL  DEPTNO
  -----------------
  KING  5000 10
  JONES 2975 20
  SCOTT 3000 20
  FORD  3000 20
  ALLEN 1600 30
  BLAKE 2850 30
  已选择 6 行
  **这条是「找出每个部门里工资高于本部门平均的人」。**
  **注意子查询里那个 `e.deptno`——它引用了外层的表，所以子查询不能先单独跑。**
  **执行方式是「逐行代入」：外层每取一行，就把这行的 `deptno` 塞进子查询算一次。**
  **所以关联子查询在概念上很慢**（外层 14 行就跑 14 次），
  **优化器通常会把它改写成连接，但别指望一定改写。**

  SQL> select e.ename, e.sal from emp e where exists (select 1 from emp x where x.mgr = e.empno) order by e.ename
  ENAME SAL
  ----------
  BLAKE 2850
  CLARK 2450
  FORD  3000
  JONES 2975
  KING  5000
  SCOTT 3000
  已选择 6 行
  **`exists` 只关心「有没有」，不关心「是什么」**，
  **所以子查询里写 `select 1` 就够了，写 `select *` 也一样。**
  **它是「找到一行就停」，不是「全部扫完」。**

  SQL> select e.ename from emp e where not exists (select 1 from emp x where x.mgr = e.empno)
  ENAME
  ------
  SMITH
  ALLEN
  WARD
  MARTIN
  TURNER
  ADAMS
  JAMES
  MILLER
  已选择 8 行
  **`not exists` 在 NULL 上是安全的**——它只看「有没有匹配行」，
  **不做 `<> NULL` 这种比较，所以不会踩上面那个坑。**
  **这也是为什么老手见到 `not in` 就皱眉、见到 `not exists` 就放心。**

单行子查询那条：sal > (select avg(sal))。 括号里只返回一个数，所以外层能直接用 >。 这是子查询最标准的用法，也是最好懂的。 第二条看着像「找出和 SMITH 同工种的人」，实际返回了 4 行——包括 SMITH 自己。 要排除自己得加 and ename <> 'SMITH'。 这类「忘了排除自己」的疏漏，在写「找同类」的查询时很常见。 第三条要注意：SQLite 没报错，只返回了 SMITH 一行。 因为 sal = (子查询) 要求子查询只返回一行，而 job='CLERK' 有 4 个人。 SQLite 悄悄取了第一个值 800 去比，于是只匹配到 SMITH。 Oracle 会直接报 ORA-01427，一个结果都不给你。 in 那一条用的是「DALLAS 有哪些部门」——子查询先查出部门号，外层再用 in 筛选。 两层是独立的，子查询不依赖外层，可以单独跑。 ALL／ANY 那一段本沙箱跑不了——SQLite 没有这两个关键字。 我用 max／min 写了等价版，结果完全一样：> all 就是 > max，> any 就是 > min。 这不是近似，是严格等价。 not in 那一段是本篇的重点。 先给 0 行，加了 where mgr is not null 之后给 8 行。 mgr 里只有一个 NULL，整条查询就报废了。 记住这个数字对比：1 个 NULL 抵掉 8 行结果。 exists 那条返回 6 行——「这些人是别人的上级」。 not exists 返回 8 行——「这些人不是任何人的上级」。 6 加 8 等于 14，正好是总人数，说明这两条把所有人分成了互斥的两类。

30 秒实验

1. 把 not in (select mgr from emp) 换成 not in (select mgr from emp where mgr is not null)， 确认从 0 行变 8 行。然后再换成 not exists 的写法，确认结果一样。
2. 给「和 SMITH 同工种」那条加上 and ename <> 'SMITH'，看行数从 4 变 3。 「找同类但排除自己」几乎总是需要这一句。
3. 把关联子查询改成「部门平均工资」的写法： where sal > (select avg(sal) from emp x where x.deptno = e.deptno)。 注意里面的 e.deptno 就是「里外引用」的那一下。
4. 用 in 和 exists 各写一条「查出有员工的部门」，对比结果是否一样。 大多数情况下等价，但子查询里有 NULL 时就不一定了。

常见误区

1. 用 not in 时不管子查询有没有 NULL。 只要有一个 NULL，整条查询返回空。 这不是 bug，是 SQL 三值逻辑的必然结果。
2. 单行子查询返回多行还硬跑。 Oracle 报 ORA-01427， SQLite 悄悄取第一行——后者更危险，因为你不看结果根本发现不了。
3. 以为 in 和 exists 永远等价。 大多数时候是， 但子查询结果含 NULL 时，not in 和 not exists 的行为完全不同。
4. 到处写关联子查询还以为优化器一定改写。 概念上它是「外层几行就跑几次」。 数据量大时先看执行计划，别靠猜。

下一篇讲写操作：insert、update、delete，以及删错了怎么救。重点不是语法， 是「条件写错就改了一大片」这件事怎么防。

• ☐ （本篇完，下一篇 4.6 操作数据表）

相关笔记

• 检索数据
• Oracle 常用系统函数
• 用户模式
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引