SQL子查询详解：从基础到高级应用

1. 子查询基础概念解析

子查询（Subquery）是嵌套在另一个SQL查询语句中的SELECT语句，它就像是一个查询中的查询。想象一下，你要在一家大型超市里找东西，先要确定哪个区域有你需要的东西（这就是主查询），然后再在这个区域里寻找具体的商品（这就是子查询）。

子查询通常出现在WHERE、FROM或SELECT子句中，最常见的用法是在WHERE子句中作为过滤条件。比如你想找出销售额高于平均销售额的员工，就需要先计算平均销售额（子查询），再用这个结果去筛选员工（主查询）。

注意：子查询必须始终放在括号内，这是SQL语法强制要求的。忘记加括号是最常见的语法错误之一。

2. 子查询的四种主要类型

2.1 WHERE子句中的子查询

这是最常用的子查询形式，用于过滤主查询的结果。例如：

SELECT product_name, price FROM products WHERE price > (SELECT AVG(price) FROM products);

这个查询先计算所有产品的平均价格（子查询），然后返回价格高于这个平均值的产品信息。

2.2 FROM子句中的子查询

这种子查询被称为派生表或内联视图，它就像一个临时表：

SELECT dept_name, avg_salary FROM ( SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id ) AS dept_avg JOIN departments ON dept_avg.department_id = departments.department_id;

提示：FROM子句中的子查询必须要有别名，否则会报错。上面的"AS dept_avg"就是给子查询结果起的别名。

2.3 SELECT子句中的子查询

这种子查询会为结果集的每一行返回一个值：

SELECT employee_id, salary, (SELECT AVG(salary) FROM employees) as avg_salary, salary - (SELECT AVG(salary) FROM employees) as diff_from_avg FROM employees;

2.4 关联子查询

关联子查询是指子查询中引用了外部查询的列：

SELECT e1.employee_id, e1.salary FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id );

这个查询找出每个部门中工资高于本部门平均工资的员工。

3. 子查询练习题详解

3.1 基础练习题

题目1：找出工资高于公司平均工资的员工

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

题目2：找出没有订单的客户

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

注意：当使用IN或NOT IN时，要确保子查询返回的列是单列，否则会报错。

3.2 中级练习题

题目3：找出每个部门工资最高的员工

SELECT e.employee_id, e.employee_name, e.department_id, e.salary FROM employees e WHERE e.salary = ( SELECT MAX(salary) FROM employees WHERE department_id = e.department_id );

题目4：找出销售额超过部门平均销售额的订单

SELECT o.order_id, o.amount, e.department_id FROM orders o JOIN employees e ON o.employee_id = e.employee_id WHERE o.amount > ( SELECT AVG(o2.amount) FROM orders o2 JOIN employees e2 ON o2.employee_id = e2.employee_id WHERE e2.department_id = e.department_id );

3.3 高级练习题

题目5：找出连续三年销售额增长的客户

SELECT c.customer_id, c.customer_name FROM customers c WHERE ( SELECT COUNT(*) FROM ( SELECT YEAR(order_date) as year, SUM(amount) as total FROM orders WHERE customer_id = c.customer_id AND YEAR(order_date) BETWEEN YEAR(CURRENT_DATE) - 3 AND YEAR(CURRENT_DATE) - 1 GROUP BY YEAR(order_date) HAVING COUNT(*) > 0 ) yearly_sales ) = 3 AND ( SELECT SUM(amount) FROM orders WHERE customer_id = c.customer_id AND YEAR(order_date) = YEAR(CURRENT_DATE) - 3 ) < ( SELECT SUM(amount) FROM orders WHERE customer_id = c.customer_id AND YEAR(order_date) = YEAR(CURRENT_DATE) - 2 ) AND ( SELECT SUM(amount) FROM orders WHERE customer_id = c.customer_id AND YEAR(order_date) = YEAR(CURRENT_DATE) - 2 ) < ( SELECT SUM(amount) FROM orders WHERE customer_id = c.customer_id AND YEAR(order_date) = YEAR(CURRENT_DATE) - 1 );

题目6：找出每个产品类别中价格排名前三的产品

SELECT p1.product_id, p1.product_name, p1.category_id, p1.price FROM products p1 WHERE ( SELECT COUNT(*) FROM products p2 WHERE p2.category_id = p1.category_id AND p2.price >= p1.price ) <= 3 ORDER BY p1.category_id, p1.price DESC;

4. 子查询性能优化技巧

4.1 使用EXISTS代替IN

当子查询可能返回大量数据时，EXISTS通常比IN性能更好：

-- 低效写法 SELECT customer_id, customer_name FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE order_date > '2023-01-01' ); -- 高效写法 SELECT customer_id, customer_name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date > '2023-01-01' );

4.2 避免在SELECT子句中使用子查询

SELECT子句中的子查询会对结果集的每一行都执行一次，当数据量大时性能会很差。可以尝试用JOIN重写：

-- 低效写法 SELECT e.employee_id, e.salary, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) as dept_avg FROM employees e; -- 高效写法 SELECT e.employee_id, e.salary, d.avg_salary as dept_avg FROM employees e JOIN ( SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id;

4.3 使用CTE（公用表表达式）提高可读性

对于复杂的子查询，使用CTE可以让SQL更易读和维护：

WITH dept_avg AS ( SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id ), high_earners AS ( SELECT employee_id FROM employees e JOIN dept_avg d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary * 1.5 ) SELECT e.employee_id, e.employee_name, e.salary, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.employee_id IN (SELECT employee_id FROM high_earners);

5. 常见错误与解决方案

5.1 子查询返回多行错误

当子查询可能返回多行时，不能使用比较运算符（=, >, <等），应该使用IN、ANY或ALL：

-- 错误写法（如果子查询返回多行会报错） SELECT product_name FROM products WHERE price = (SELECT price FROM special_offers); -- 正确写法 SELECT product_name FROM products WHERE price IN (SELECT price FROM special_offers);

5.2 忽略NULL值的影响

NOT IN子查询如果包含NULL值可能会返回意外结果：

-- 这个查询可能不会返回任何结果，如果子查询结果中有NULL的话 SELECT customer_id FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);

解决方案是使用NOT EXISTS或在子查询中过滤掉NULL：

-- 解决方案1：使用NOT EXISTS SELECT customer_id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); -- 解决方案2：过滤NULL SELECT customer_id FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM orders WHERE customer_id IS NOT NULL );

5.3 关联子查询性能问题

关联子查询对外部查询的每一行都会执行一次，当数据量大时性能会很差：

-- 低效的关联子查询 SELECT e.employee_id, e.salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e.department_id ); -- 高效写法：使用JOIN SELECT e.employee_id, e.salary FROM employees e JOIN ( SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;

6. 实际业务场景应用案例

6.1 电商平台分析

场景：找出购买金额超过该客户平均订单金额的订单

SELECT o.order_id, o.customer_id, o.amount, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.amount > ( SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id );

6.2 人力资源管理系统

场景：找出每个部门中工资排名前10%的员工

SELECT e.employee_id, e.employee_name, e.department_id, e.salary FROM employees e WHERE ( SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary >= e.salary ) <= ( SELECT COUNT(*) * 0.1 FROM employees e3 WHERE e3.department_id = e.department_id );

6.3 学校成绩管理系统

场景：找出每门课程成绩高于该课程平均成绩的学生

SELECT s.student_id, s.student_name, c.course_name, sc.score FROM student_courses sc JOIN students s ON sc.student_id = s.student_id JOIN courses c ON sc.course_id = c.course_id WHERE sc.score > ( SELECT AVG(score) FROM student_courses WHERE course_id = sc.course_id );

7. 子查询与JOIN的选择

虽然很多子查询可以用JOIN重写，但两者各有适用场景：

• 使用子查询更适合的情况：

  • 需要判断存在性（EXISTS/NOT EXISTS）
  • 需要与聚合值比较（如大于平均值）
  • 查询逻辑复杂，用JOIN难以表达
  • 子查询结果很小而主查询结果很大

• 使用JOIN更适合的情况：

  • 需要从多个表获取列
  • 子查询结果集很大
  • 查询需要多次引用同一个子查询结果（可以用JOIN+临时表）

示例对比：

-- 子查询版本 SELECT product_id, product_name FROM products WHERE category_id IN ( SELECT category_id FROM categories WHERE category_name LIKE '%电子%' ); -- JOIN版本 SELECT p.product_id, p.product_name FROM products p JOIN categories c ON p.category_id = c.category_id WHERE c.category_name LIKE '%电子%';

在这个例子中，JOIN版本通常更高效，特别是当categories表有适当的索引时。