12.1 子查询基础

      在编写 SQL 查询时，我们经常会遇到这样的情况：一个查询的结果需要作为另一个查询的条件或数据来源。这时，就可以使用子查询。子查询是指嵌套在另一个查询（外部查询）中的查询，它可以出现在 SELECT、FROM、WHERE、HAVING、EXISTS 等子句中。掌握子查询，能让你的 SQL 能力更上一层楼。

一、什么是子查询？

子查询 就是一个完整的 SELECT 语句，但它被括号 () 包围，并嵌入到外部查询中。外部查询可以使用子查询返回的结果来进一步筛选、计算或构建数据。

例如，我们想查询工资高于公司平均工资的员工。先要知道平均工资是多少，然后拿每个员工的工资与之比较。这个“平均工资”就可以通过子查询得到：

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

这里 (SELECT AVG(salary) FROM employees) 就是一个子查询，它先计算出平均工资，然后外部查询用这个结果进行过滤。

二、子查询的分类

根据返回结果的不同，子查询可以分为三类：

1、 标量子查询

返回结果：返回单个值（一行一列）

示例：(SELECT AVG(salary) FROM employees)

2、 行子查询

返回结果：返回一行多列

示例：(SELECT min_salary, max_salary FROM job_limits WHERE job_id = 1)

3、 表子查询

返回结果：返回多行多列（虚拟表）

示例：(SELECT id, name FROM employees WHERE department_id = 10)

根据与外部查询的相关性，子查询还可分为：

• 非相关子查询：独立于外部查询，可以单独运行。上面的平均工资例子就是非相关的。

• 相关子查询：引用了外部查询的列，必须为外部查询的每一行执行一次。稍后会详细介绍。

三、子查询的位置

子查询可以出现在 SQL 语句的多个地方，最常见的有：

1. WHERE 子句中

用于过滤条件，常与比较运算符（=、>、<、IN、EXISTS 等）结合。

-- 查询工资最高的员工
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- 查询在销售部工作的员工
SELECT name
FROM employees
WHERE department_id = (SELECT id FROM departments WHERE name = '销售部');

2. FROM 子句中

将子查询的结果作为一张临时表（派生表），必须起别名。

-- 查询各部门平均工资，并筛选平均工资大于5000的部门
SELECT dept_id, avg_salary
FROM (SELECT department_id AS dept_id, AVG(salary) AS avg_salary
      FROM employees
      GROUP BY department_id) AS dept_avg
WHERE avg_salary > 5000;

3. SELECT 子句中

作为输出列的一部分，通常返回标量值。

-- 查询员工姓名、工资以及公司平均工资
SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg_salary
FROM employees;

4. HAVING 子句中

用于分组后的条件过滤。

-- 查询平均工资高于全体平均工资的部门
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

5. EXISTS 子句中

EXISTS 用于检查子查询是否返回任何行，常用于相关子查询。

-- 查询有员工的部门
SELECT name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);

四、标量子查询详解

标量子查询返回单个值（一行一列），因此可以出现在任何期望单个值的地方，比如 SELECT 列表、WHERE 的比较条件等。

示例：查询每个员工的姓名、工资以及该员工所在部门的平均工资。

SELECT 
    name,
    salary,
    (SELECT AVG(salary) 
     FROM employees e2 
     WHERE e2.department_id = e1.department_id) AS dept_avg_salary
FROM employees e1;

这里的子查询是相关的，因为它引用了外部表的 department_id。对外部每一行，子查询都会执行一次。

五、行子查询

行子查询返回一行多列，可以用行构造函数与外部进行比较。

-- 假设有一张表记录每个部门的最低和最高工资限额
SELECT department_id, name, salary
FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

这个查询找出每个部门工资最高的员工。子查询返回多行，每行两列 (department_id, MAX(salary))，外部用 IN 来匹配。

六、表子查询（派生表）

表子查询返回多行多列，必须放在 FROM 后面，并赋予别名，就像一个临时表。

示例：查询每个部门工资排名前两名的员工（假设每个部门至少两人）。

SELECT d.name AS department, e.name, e.salary
FROM employees e
JOIN (
    SELECT department_id, 
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
    FROM employees
) ranked ON e.id = ranked.id
WHERE ranked.rn <= 2;

这里子查询先为每个部门的员工按工资降序编号，外部再连接并筛选编号 ≤2 的行。

七、相关子查询 vs 非相关子查询

非相关子查询

• 独立运行，不依赖外部查询。

• 子查询只执行一次，结果用于外部查询。

• 性能通常较好。

相关子查询

• 引用了外部查询的列。

• 对于外部查询的每一行，子查询都可能执行一次（取决于优化器）。

• 如果外部表很大，性能可能较差，但有时无法避免。

示例：找出每个部门工资最高的员工（另一种写法）

SELECT name, salary, department_id
FROM employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

这里子查询必须为外部每一行执行（虽然优化器可能优化），因为 e1.department_id 在变化。

八、子查询的注意事项

1. 标量子查询必须确保只返回一行一列，否则会报错。如果返回多行，可以使用 IN 或 EXISTS 等。

2. 子查询中不能直接使用外部表的别名（除非是相关子查询）。在非相关子查询中，不能引用外部表的列。

3. 性能考虑：子查询，特别是相关子查询，可能导致性能问题。对于大数据集，可以考虑用 JOIN 重写。

4. NULL 的处理：如果子查询返回 NULL，比较运算符（如 =）的结果是 UNKNOWN，可能导致意外结果。用 IN 或 EXISTS 会更安全。

5. 子查询可以嵌套多层，但嵌套越深，可读性和性能越差，应尽量简化。

九、练习

1. 查询工资低于所在部门平均工资的员工。

2. 查询没有下过单的客户（使用 NOT EXISTS）。

3. 使用派生表查询每个产品类别中价格最高的产品。

十、小结

• 子查询 是嵌套在另一个 SQL 查询中的查询，用括号包围。

• 根据返回类型分为标量子查询、行子查询、表子查询。

• 可以出现在 SELECT、FROM、WHERE、HAVING、EXISTS 等子句中。

• 相关子查询 引用外部列，为每一行执行；非相关子查询 独立运行。

• 合理使用子查询能简化复杂查询，但要注意性能和可读性。