SQL 子查询：完整指南与示例

什么是子查询？

子查询（Subquery）是嵌套在另一个SQL查询中的查询，也称为内部查询或嵌套查询。子查询可以出现在SELECT、FROM、WHERE、HAVING等子句中，为主查询提供数据。

子查询的类型

1. 单行子查询

返回单行单列结果的子查询，通常与比较运算符(=, >, <, >=, <=, <>)一起使用。

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

2. 多行子查询

返回多行结果的子查询，通常与IN、ANY、ALL等运算符一起使用。

SELECT product_name, price
FROM products
WHERE product_id IN (SELECT product_id FROM order_items WHERE quantity > 10);

3. 多列子查询

返回多列结果的子查询。

SELECT employee_id, first_name, last_name
FROM employees
WHERE (department_id, salary) IN 
      (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);

4. 相关子查询

子查询引用外部查询的列，为外部查询的每一行执行一次。

SELECT e.employee_id, e.last_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

子查询的位置

1. WHERE子句中的子查询

最常见的子查询位置，用于过滤数据。

SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date > '2023-01-01');

2. FROM子句中的子查询（派生表）

子查询结果作为临时表使用。

SELECT d.department_name, e.avg_salary
FROM departments d
JOIN (SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id) e
ON d.department_id = e.department_id;

3. SELECT子句中的子查询（标量子查询）

必须返回单行单列结果。

SELECT product_name, 
       price,
       (SELECT AVG(price) FROM products) as avg_price,
       price - (SELECT AVG(price) FROM products) as price_diff
FROM products;

4. HAVING子句中的子查询

对分组结果进行过滤。

SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > (SELECT AVG(emp_count) 
                   FROM (SELECT COUNT(*) as emp_count FROM employees GROUP BY department_id));

子查询运算符

1. IN / NOT IN

检查值是否在子查询结果集中。

SELECT product_name
FROM products
WHERE product_id IN (SELECT product_id FROM order_items WHERE quantity > 5);

2. EXISTS / NOT EXISTS

检查子查询是否返回任何行（更关注存在性而非具体值）。

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

3. ANY / SOME

与比较运算符一起使用，表示与子查询结果中的任意值比较。

SELECT employee_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);

4. ALL

与比较运算符一起使用，表示与子查询结果中的所有值比较。

SELECT product_name, price
FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');

子查询性能优化建议

1. 避免过度嵌套：多层嵌套子查询会影响性能
2. 使用JOIN替代：某些情况下JOIN比子查询效率更高
3. 相关子查询谨慎使用：为外部查询的每一行执行一次，可能很慢
4. 确保子查询有适当索引：特别是在WHERE和JOIN条件中使用的列
5. 限制返回数据量：子查询只返回必要的数据

实际应用示例

示例1：查找没有订单的客户

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

示例2：查找各部门薪资最高的员工

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

示例3：查找销售额高于平均销售额的产品

SELECT p.product_id, p.product_name, SUM(oi.quantity * oi.unit_price) as total_sales
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name
HAVING SUM(oi.quantity * oi.unit_price) > 
       (SELECT AVG(total_sales) 
        FROM (SELECT SUM(quantity * unit_price) as total_sales 
              FROM order_items 
              GROUP BY product_id));

掌握子查询是SQL高级应用的关键，合理使用可以解决复杂的数据查询问题，但也要注意性能影响。