子查询

第 15 课，共 21 课 · 第 4 部分 · 更强的查询 · 约 15 分钟

有的问题要分两步问：「哪些商品比平均价贵？」先得知道平均价是多少，再拿它去比。子查询就是写在另一条查询里面、用括号括起来的查询，让两步变成一条。

先算平均价

分两步做，第一步：

main.sql

1SELECT AVG(price) FROM products;

输出

AVG(price)
19.080000

1 行

平均价是 19.08。第二步把这个数抄进 WHERE：WHERE price > 19.08。能查出来，可要是商品的价格变了，抄下来的数就不对了。

WHERE 里的子查询

不抄数，直接把第一步的查询放进括号里：

main.sql

1SELECT name, price2FROM products3WHERE price > (SELECT AVG(price) FROM products)4ORDER BY price DESC;

输出

name	price
耳机	59.00
双肩包	39.90
台灯	25.00

3 行

括号里的查询先算，得出一个值 19.08，外面的 WHERE 再拿每件商品的价格和它比。这种只返回一个值的子查询，可以放在任何能写一个值的地方。

先猜一猜

这条查询查出什么？

1SELECT name2FROM products3WHERE price = (SELECT MAX(price) FROM products);

括号里先算出最高价 59.00，外面再找价格等于 59.00 的商品，查出它的 name：耳机。只写 SELECT name, MAX(price) FROM products 是不行的，聚合会把所有行并成一行，查不出是哪件商品。

子查询返回好几行

想找有待发货订单的客户。待发货的订单不止一个：

main.sql

1SELECT name2FROM customers3WHERE id = (SELECT customer_id FROM orders WHERE status = '待发货');

输出

ERROR 1242 (21000): Subquery returns more than 1 row

括号里的子查询返回了不止一行，可它用在了只能放一个值的地方。可以加 LIMIT 1，或者改用 IN (子查询)。

ERROR 1242：子查询返回了不止一行。= 只能和一个值比，括号里却查出了 3 个客户编号。

IN (子查询)

第 3 课的 IN (1, 2, 4) 是「在这几个值里」。括号里换成一个子查询，就是「在子查询查出的这些值里」：

main.sql

1SELECT id, name2FROM customers3WHERE id IN (SELECT customer_id FROM orders WHERE status = '待发货')4ORDER BY id;

输出

id	name
1	林晓
2	王磊
4	张伟

3 行

前面加 NOT，就是「不在里面」。下面查出没下过单的客户，和第 12 课用 LEFT JOIN … IS NULL 查出的一样：

main.sql

1SELECT id, name2FROM customers3WHERE id NOT IN (SELECT customer_id FROM orders)4ORDER BY id;

输出

id	name
6	赵敏
9	吴昊
10	郑雪

3 行

小技巧： NOT IN 的子查询里只要有一个 NULL，整个条件就一行也查不出来（NULL 跟谁比都不是「真」）。子查询的列可能有 NULL 时，加上 WHERE 列 IS NOT NULL，或者改用下面的 NOT EXISTS。

EXISTS

EXISTS (子查询) 问的是：子查询有没有查出至少一行？有就留下，没有就不要。查有待发货订单的客户，也可以这样写：

main.sql

1SELECT c.id, c.name2FROM customers AS c3WHERE EXISTS (4    SELECT *5    FROM orders AS o6    WHERE o.customer_id = c.id AND o.status = '待发货'7)8ORDER BY c.id;

输出

id	name
1	林晓
2	王磊
4	张伟

3 行

注意子查询里的 c.id：它是外面那张表的列。外面的 customers 每看一行，括号里的查询就用这一行的 c.id 跑一次。看到 1 号林晓，就找 1 号的待发货订单，找到了，留下；看到 3 号陈静，一个也没有，不要。SELECT * 写什么都行，EXISTS 只看有没有行。

NOT EXISTS 反过来，留下子查询一行也查不出的那些。

FROM 里的子查询

子查询的结果也是一张表，所以可以放在 FROM 后面，再在它上面查。比如订单的平均金额：先算出每个订单的总额，再求这些总额的平均：

main.sql

1SELECT ROUND(AVG(total), 2) AS avg_order2FROM (3    SELECT order_id, SUM(quantity * unit_price) AS total4    FROM order_items5    GROUP BY order_id6) AS t;

输出

avg_order
36.14

1 行

括号里先查出 12 行，每个订单一行；外面把这 12 行当成一张叫 t 的表，求 total 的平均。这种表叫派生表。它必须有个名字，AS t 不能省：去掉它再运行，会得到 ERROR 1248，说每个派生表都要有自己的别名。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。

练习 1

比平均价便宜

查出价格低于平均价的商品，显示 name 和 price，按 price 从低到高排。平均价不要自己算好写进去。

要显示

name	price
笔记本	4.50
中性笔（10 支）	6.00
数据线	8.50
咖啡杯	9.90
帆布袋	11.00
水杯	12.00
手机壳	15.00

7 行

exercise1.sql

1SELECT name, price2FROM products3ORDER BY price;

输出

点「运行」或按 Ctrl+Enter

练习 2

待发货订单里的商品

用 IN (子查询) 查出出现在待发货订单里的商品，显示 id 和 name，按 id 排序。

要显示

id	name
1	笔记本
2	中性笔（10 支）
3	双肩包
8	咖啡杯
10	帆布袋

5 行

exercise2.sql

1SELECT oi.product_id2FROM order_items AS oi3WHERE oi.order_id IN (7, 9, 12);

输出

点「运行」或按 Ctrl+Enter

练习 3

平均每人几单

只算下过单的客户，平均每人下了几单？用 FROM 里的子查询，显示一列 avg_orders，保留 2 位小数。

要显示

avg_orders
1.71

1 行

exercise3.sql

1SELECT customer_id, COUNT(*) AS n2FROM orders3GROUP BY customer_id;

输出

点「运行」或按 Ctrl+Enter

这一课学会了

子查询是写在括号里的查询，先算出来，再给外面用。
返回一个值的子查询能放在 WHERE 里比较；返回好几行时用 = 会得到 ERROR 1242。
IN (子查询) 和 NOT IN (子查询) 拿一列值来筛选；NOT IN 碰上 NULL 会什么都查不出。
EXISTS (子查询) 只问有没有行；子查询里可以用外面那一行的列。
FROM 里的子查询是派生表，必须起别名，否则 ERROR 1248。