子查询 - SQL 数据库

01. 子查询是什么

子查询说白了就是查询套查询——一个 SELECT 语句嵌在另一个 SELECT 里面。你可以把内部的查询结果当成一个临时表，外层的查询再对这个临时表进行操作。打个比方：你想找班上比平均分高的同学，得先算出平均分（内层），再用每个人的分数跟平均分比（外层）。子查询就是干这事的。子查询可以出现在 SELECT、FROM、WHERE、HAVING 这些地方，用得最多的是 WHERE 子句里。

sql

-- 找工资比全体员工平均工资高的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

02. 标量子查询（返回单个值）

标量子查询是最简单的一种——内层查询只返回一个值，就像查字典找到一个词的意思一样。通常用在 WHERE 的比较条件里（大于、小于、等于这些），或者 SELECT 的列里。常见的场景：找最大值、最小值、平均值、总数，然后拿这个值去比。但要注意如果子查询返回了空值 NULL，整个比较的结果也是 NULL，可能查不出你预期的结果。

sql

-- 找工资最高的人
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 每件商品后面显示全品类均价
SELECT name, price,
  (SELECT AVG(price) FROM products) AS avg_all
FROM products;

03. IN / NOT IN 子查询

IN 子查询就是判断某个值在不在一堆值里面。内层查询返回一列值，外层看某个字段在这个集合里就选出来。典型场景：查买了某个商品的用户、查有订单的客户、查某个部门下的员工。IN 后面可以是子查询，也可以是直接写死的列表（IN (1,2,3)）。 NOT IN 就是反过来，不在里面才选。但要注意：如果子查询结果里有 NULL，NOT IN 可能啥也查不出来，这是最常见的坑。改 NOT EXISTS 就没事。

sql

-- 查下过订单的客户
SELECT * FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

-- 查没下过订单的客户
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

IN 子查询适合内层结果集不太大的场景。如果数据量很大，用 JOIN 通常比 IN 快。

NOT IN 时子查询结果不能含 NULL，否则整个 NOT IN 判断全返回 NULL，等于啥都没选出来。

04. EXISTS / NOT EXISTS 子查询

EXISTS 跟 IN 类似，但更灵活。它不关心子查询返回什么值，只关心有没有结果。只要子查询能查出至少一行，EXISTS 就认为条件成立。 EXISTS 的执行逻辑是半连接——外层每查一行，就到内层去跑一次看有没有匹配。但优化器通常会把它转换成 JOIN，效率并不差。 NOT EXISTS 比 NOT IN 安全，因为不怕 NULL 的问题。在判断没有关联记录的时候，NOT EXISTS 是首选。EXISTS 子查询里写 SELECT 1、SELECT 星号都一样。

sql

-- 查有订单的客户
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 查没被任何人买过的商品
SELECT * FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

EXISTS 子查询里写 SELECT 1、SELECT 星号都一样，因为不管返回值，只看有没有行。

05. 关联子查询（Correlated Subquery）

关联子查询就是内层查询引用了外层查询的列——里外有联系。普通子查询（非关联）可以先执行内层再把结果传给外层，但关联子查询必须外层每出一行、内层就重新算一次。典型场景：查每个部门工资最高的人、每个分类下销量最多的商品。外层遍历每行，内层引用外层的行 ID 去算。关联子查询性能可能差，因为内层会执行很多次。很多场景可以用窗口函数替代，性能翻几倍。比如每个分组取 Top N，用 ROW_NUMBER() OVER(PARTITION BY ...) 一次扫描搞定。

sql

-- 每个部门工资最高的人（关联子查询版）
SELECT name, department, salary
FROM employees e1
WHERE salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

-- 窗口函数版，性能更好
SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY department ORDER BY salary DESC) r
  FROM employees
) t WHERE r = 1;

关联子查询的常见优化方向就是用窗口函数或 JOIN 替代，尤其是数据量大的时候。

知识测验

第 1/5 题正确 0

子查询和 JOIN 最大的不同是什么？

索引与优化