把一条查询的结果当作另一条查询的输入:WHERE 子查询、IN/NOT IN、EXISTS 与相关子查询。

子查询:查询里套查询

一个查询的结果可以给另一个查询当输入,括号里套的就是子查询(Subquery):

sql
SELECT ... FROM 表 WHERE 列 比较符 (SELECT ...);

标量子查询:结果只有一个值

「哪些书比全店平均价贵?」平均价是一个数,可以先算它:

sql
SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);

子查询只返回一个值(一行一列)时叫标量子查询,可以像普通值一样放在比较符右边。

IN 子查询:结果是一列值

「谁买过《三体》?」订单明细里有 book_id,但顾客名在 customers。一步到位:先找出《三体》的 book_id,再找买过它的订单,再找下单顾客——三层 IN:

sql
SELECT name FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_id IN ( SELECT order_id FROM order_items WHERE book_id IN ( SELECT book_id FROM books WHERE title = '三体' ) ) );

从里往外读:三体的编号 → 出现在哪些订单 → 这些订单是谁下的 → 顾客姓名。

NOT IN 与 NOT EXISTS:找「没有」的记录

「哪些书从来没被订购过?」用 NOT IN 排除出现过的 book_id:

sql
SELECT title FROM books WHERE book_id NOT IN ( SELECT book_id FROM order_items WHERE book_id IS NOT NULL );

NULL 陷阱:如果子查询结果里混入 NULL,NOT IN 会返回空结果(NULL 参与比较永远得到「未知」)。稳妥的写法是子查询里排除 NULL,或者用 NOT EXISTS。

NOT EXISTS 写法与表结构无关,也更加通用:

sql
SELECT b.title FROM books b WHERE NOT EXISTS ( SELECT 1 FROM order_items oi WHERE oi.book_id = b.book_id );

相关子查询:内外有关联

子查询里引用了外层查询的列,就叫相关子查询——外层每行都要执行一次子查询。「每个分类中最贵的书」:

sql
SELECT category, title, price FROM books WHERE price = ( SELECT MAX(price) FROM books b2 WHERE b2.category = books.category );

外层每本书都会问一句:「在我这个分类里,最贵的价格是多少?我的价格正好是它吗?」

EXISTS:只关心有没有

「下过至少 2 单的顾客」——对每位顾客检查是否存在满足条件的记录:

sql
SELECT name FROM customers c WHERE EXISTS ( SELECT o.customer_id FROM orders o WHERE o.customer_id = c.customer_id GROUP BY o.customer_id HAVING COUNT(*) >= 2 );

EXISTS 不看子查询返回什么值,只看有没有行返回;有就是真。这种题通常也能用 GROUP BY + HAVING 写出等价查询——判题器只比对结果,两种写法都会通过。

小结

  • 标量子查询(一个值)可放比较符右边
  • IN / NOT IN 接一列值;子查询有 NULL 时慎用 NOT IN
  • 相关子查询引用外层列,逐行执行
  • EXISTS / NOT EXISTS 只判断存在性,语义最稳

练习题

建议先在本地 SQL 环境或浏览器 SQLite 练习本中写一遍,再展开参考答案对照。

1. 比平均价贵的书

查询价格高于全店平均价的书的 title 和 price(用子查询算平均价)。

💡 提示:WHERE price > (SELECT AVG(price) FROM books)

查看参考答案
sql
SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);

2. 谁买过《三体》

查询购买过《三体》的顾客姓名(多层嵌套 IN)。

💡 提示:书名 → book_id → 订单明细 → 订单 → customer_id → 顾客姓名,从里往外一层层套。

查看参考答案
sql
SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_id IN (SELECT order_id FROM order_items WHERE book_id IN (SELECT book_id FROM books WHERE title = '三体')));

3. 从未被订购的书

查询从未出现在任何订单明细里的书的 title(注意避开 NULL 陷阱)。

💡 提示:NOT IN 的子查询里加 WHERE book_id IS NOT NULL,或者用 NOT EXISTS。

查看参考答案
sql
SELECT title FROM books WHERE book_id NOT IN (SELECT book_id FROM order_items WHERE book_id IS NOT NULL);

4. 每个分类的扛把子

查询每个分类中价格最高的书:输出 category、title、price(用相关子查询)。

💡 提示:外层判断 price = (SELECT MAX(price) FROM books b2 WHERE b2.category = books.category)

查看参考答案
sql
SELECT category, title, price FROM books WHERE price = (SELECT MAX(price) FROM books b2 WHERE b2.category = books.category);

5. 下单两次以上的顾客

查询下过至少 2 张订单的顾客姓名(EXISTS 或 GROUP BY + HAVING 均可)。

💡 提示:EXISTS 子查询里 GROUP BY o.customer_id HAVING COUNT(*) >= 2。

查看参考答案
sql
SELECT name FROM customers c WHERE EXISTS (SELECT o.customer_id FROM orders o WHERE o.customer_id = c.customer_id GROUP BY o.customer_id HAVING COUNT(*) >= 2);

涉及的数据表

  • books
  • customers
  • orders
  • order_items

由在线工具箱(www.vba.net)整理制作