返回一个值的子查询
凡是能写一个值的地方，都可以改写成一条只返回一行一列的查询。
1
SELECT name,
price,
ROUND((SELECT AVG(price) FROM products), 2) AS average_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 20;结果 — 共 20 行，这里是前 5 行
| name | price | average_price |
|---|---|---|
| Glide GPS Watch Pro | 2484.4 | 1230.21 |
| Terra 3D Puzzle | 2481.77 | 1230.21 |
| Aria Castle Kit | 2479.41 | 1230.21 |
| Aria LED Strip Pro | 2463.41 | 1230.21 |
| Nova Brief Answers | 2445.49 | 1230.21 |
浏览器会另开一个标签页打开 Planche，示例数据库已经载入，这条查询也已经放好。本页其他示例都会复用同一个标签页；在你按下 F9 之前，什么都不会执行。
(SELECT AVG(price) FROM products) 产生一个数字，查询的其余部分就当你直接把那个数字敲进去了一样。这也回答了"为什么不能写 WHERE price > AVG(price)"——聚合汇总的是一个组，而 WHERE 运行时还没有组。子查询绕开了这一点：它是一条自成一体的完整查询，在外层开始筛选之前就已经算完了。
返回一列值的子查询
2
SELECT first_name, last_name, email, country
FROM customers
WHERE id IN (
SELECT customer_id
FROM orders
WHERE status = 'refunded'
)
ORDER BY last_name
LIMIT 20;结果 — 共 20 行，这里是前 5 行
| first_name | last_name | country | |
|---|---|---|---|
| Maya | Ahmed | maya.ahmed1@mail.example | US |
| Noah | Ahmed | noah.ahmed@inbox.example | FR |
| Liam | Ahmed | liam.ahmed1@mail.example | DE |
| Aisha | Ahmed | aisha.ahmed@mail.example | GB |
| Sofia | Ahmed | sofia.ahmed@example.com | ES |
内层查询给出一列客户 id，IN 判断是否在其中。从里往外读——"id 出现在退款订单的客户 id 之中的那些客户"。
EXISTS：只问有没有匹配上
3
SELECT c.first_name, c.last_name, c.country
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.total > 1000
)
ORDER BY c.last_name
LIMIT 20;结果 — 共 20 行，这里是前 5 行
| first_name | last_name | country |
|---|---|---|
| Aisha | Ahmed | GB |
| Aisha | Ahmed | JP |
| Ava | Ahmed | IT |
| Emma | Ahmed | IN |
| Ethan | Ahmed | FR |
EXISTS 接一条查询，只要它至少返回一行就为真。写 SELECT 1 是惯例：没有人看被选出来的值，只看有没有行回来，所以选真实的列没有意义。
这条子查询引用了外层的 c.id，这使它成为相关子查询——它对每个客户都是一个不同的问题，而不是只算一次的一份列表。要问"有没有相关的行存在"，这通常既是最清楚的写法，也是引擎优化得最好的写法。
NOT IN 的陷阱
120 名员工里有 110 人手下没人。这条查询就是要找他们：
4
-- Employees nobody reports to. 110 of the 120 qualify,
-- and this query finds none of them.
SELECT first_name, last_name, position
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);没有匹配的行
零行。也不报错。
manager_id 允许为空，组织架构最上面那十个人在这一列上是 NULL。于是内层查询返回的列表里含有一个 NULL，而 x NOT IN (1, 2, NULL) 问的是"x 是否既不等于 1，也不等于 2，也不等于这个未知的值？"。最后一段是未知，整个条件就是未知，于是没有任何一行会被留下——对每一个 x 都是如此。列表里任何位置只要有一个 NULL，结果就空了。
IN 不会这样被拖垮：只要某一行匹配上了一个真实的值，它就会被留下，列表里还有什么都无所谓。只有取反的形式要求所有比较都为真，所以一个未知就足以把结果清空。
解法要么是在子查询里加上 WHERE manager_id IS NOT NULL，要么——更好，因为不会忘——换成 NOT EXISTS：
5
SELECT e.first_name, e.last_name, e.position
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM employees m
WHERE m.manager_id = e.id
)
ORDER BY e.last_name
LIMIT 20;结果 — 共 20 行，这里是前 5 行
| first_name | last_name | position |
|---|---|---|
| Priya | Ahmed | Senior Engineer |
| Diego | Ahmed | Intern |
| Yuki | Ahmed | Engineer |
| Ravi | Brown | Lead |
| Jonas | Brown | Specialist |
NOT EXISTS 问的是行，不是值，所以 NULL 根本不会进入比较。默认就伸手去拿 NOT EXISTS，能一次性消掉一整类悄无声息的错误答案。
把子查询当表用
返回多行多列的查询，可以放在本该写表的位置，也就是 FROM 里。这样的查询叫派生表。
6
SELECT category, products, average
FROM (
SELECT cat.name AS category,
COUNT(*) AS products,
ROUND(AVG(p.price), 2) AS average
FROM products p
JOIN categories cat ON cat.id = p.category_id
GROUP BY cat.id
)
WHERE products >= 10
ORDER BY average DESC;结果 — 共 26 行，这里是前 5 行
| category | products | average |
|---|---|---|
| Wearables | 11 | 1672.29 |
| Programming | 10 | 1585.67 |
| Tablets | 17 | 1529.45 |
| Audio | 11 | 1482.89 |
| Kitchen | 12 | 1443.21 |
这是"我想按聚合值筛选"的另一个答案：在内层分组，再像对待普通表一样筛选结果。它干的活和 HAVING 一样，而且还能按别名筛选，这一点 HAVING 做不到可移植的版本。
派生表可以嵌套，而嵌套正是它变得难读的地方：套上三层，就只能从里往外读了。下一课会把它换成一种从上往下读的写法。
每行一条子查询
现在你能做什么
除了把复杂查询写得好读，其他都能做了——而那正是下一课的内容。