12.2 WHERE子句中的子查询

      子查询最常见的用途之一就是出现在 WHERE 子句中，作为筛选条件的一部分。通过将子查询的结果与外部表的列进行比较，我们可以实现更加动态和复杂的过滤逻辑。

一、子查询在WHERE中的基本形式

SELECT 列名 FROM 表名 WHERE 表达式 运算符 (子查询);

其中：

• 表达式 通常是外部表的某个列或基于列的计算。

• 运算符 可以是比较运算符（=, >, <, >=, <=, <>），也可以是 IN、EXISTS、ANY、ALL 等。

• 子查询 必须用括号包围，并遵循子查询的规则（根据运算符的不同，子查询返回的行列数有不同要求）。

二、与比较运算符结合

当子查询返回单个值（标量子查询）时，可以直接与比较运算符一起使用。

示例1：查询工资高于平均工资的员工

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

这里的子查询 (SELECT AVG(salary) FROM employees) 返回一个数值，外部查询将每个员工的工资与之比较。

示例2：查询入职日期最晚的员工

SELECT name, hire_date
FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

注意事项

• 标量子查询必须确保只返回一行一列，否则会引发运行时错误。

• 如果子查询可能返回 NULL，比较结果会变成 UNKNOWN，导致该行被过滤掉。可以用 COALESCE 或 IS NOT NULL 处理。

三、与 IN 操作符结合

当子查询返回一列多行时，可以使用 IN 或 NOT IN 来判断外部表达式的值是否在子查询的结果集中。

示例3：查询在销售部或市场部工作的员工

SELECT name, department_id
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE name IN ('销售部', '市场部')
);

示例4：查询从未下过单的客户

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
);

关于 NULL 的陷阱

如果子查询的结果集中包含 NULL，NOT IN 的行为可能出乎意料。因为 x NOT IN (1, 2, NULL) 等价于 x != 1 AND x != 2 AND x != NULL，而 x != NULL 的结果是 UNKNOWN，整个条件变成 UNKNOWN，导致永远返回空结果。因此，使用 NOT IN 时，务必确保子查询的结果集不包含 NULL，或者用 NOT EXISTS 替代。

-- 安全写法：使用 NOT EXISTS
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

四、与 ANY 和 ALL 操作符结合

ANY 和 ALL 必须与比较运算符一起使用，用于将外部值与子查询返回的一组值进行比较。

• > ANY：大于子查询结果中的任意一个值（即大于最小值）。

• > ALL：大于子查询结果中的所有值（即大于最大值）。

示例5：查询工资高于“销售部”任意一个员工的员工

SELECT name, salary, department_id
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = (SELECT id FROM departments WHERE name = '销售部')
);

等价于：工资大于销售部的最低工资。

示例6：查询工资高于“销售部”所有员工的员工

SELECT name, salary, department_id
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = (SELECT id FROM departments WHERE name = '销售部')
);

等价于：工资大于销售部的最高工资。

注意事项

• 如果子查询返回空集，> ALL 条件为真（因为所有比较都成立），而 > ANY 条件为假。需要根据业务逻辑判断是否要考虑空集情况。

五、与 EXISTS 操作符结合

EXISTS 用于检查子查询是否返回至少一行。它通常与相关子查询结合使用，即子查询中引用了外部表的列。

示例7：查询有订单的客户

SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

对于 customers 表中的每一行，子查询检查该客户的订单是否存在。如果存在，EXISTS 返回 TRUE，该客户被选中。

示例8：查询从未下过单的客户

SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

为什么推荐 EXISTS 而不是 IN？

• EXISTS 通常比 IN 更高效，尤其当子查询表很大时，因为 EXISTS 可以在找到第一个匹配行后立即停止扫描。

• EXISTS 对 NULL 的处理更直观，不会出现 NOT IN 的陷阱。

• EXISTS 可以处理更复杂的连接条件，不仅仅是等值比较。

六、性能注意事项

1. 相关子查询的效率：相关子查询会为外部表的每一行执行一次，如果外部表很大，性能可能很差。对于此类查询，可以尝试用 JOIN 重写。

2. 索引优化：子查询中涉及的列（尤其是连接列）应建立索引，以加速查找。

3. 避免使用 IN (SELECT ...) 当子查询结果集很大时，IN 可能性能不佳，改用 EXISTS 或连接。

4. 使用 EXPLAIN 分析查询计划，观察子查询是如何执行的。

七、综合示例

假设有三个表：

• students（学生）：id, name, class_id

• classes（班级）：id, class_name

• scores（成绩）：student_id, course, score

需求1：找出所有有不及格成绩（<60）的学生

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM scores sc
    WHERE sc.student_id = s.id AND sc.score < 60
);

需求2：找出每个班级中分数最高的学生（使用相关子查询）

SELECT s.name, s.class_id, sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id
WHERE sc.score = (
    SELECT MAX(score)
    FROM scores sc2
    WHERE sc2.student_id = s.id
);

注意：这里假设每个学生只有一条成绩记录（简化），实际可能有多科，需要适当调整。

需求3：查询平均成绩高于全校平均成绩的班级

SELECT class_id, AVG(score) AS avg_score
FROM students s
JOIN scores sc ON s.id = sc.student_id
GROUP BY class_id
HAVING AVG(score) > (SELECT AVG(score) FROM scores);

八、小结

在 WHERE 子句中使用子查询可以极大地扩展查询的灵活性。你可以根据子查询返回的单值、多值或存在性来过滤数据。主要形式包括：

• 标量子查询 与比较运算符配合。

• 多值子查询 与 IN、ANY、ALL 配合。

• 存在性子查询 与 EXISTS/NOT EXISTS 配合。

理解每种形式的适用场景和潜在陷阱，并根据数据量和性能需求选择合适的写法，是写出高效 SQL 的关键。