子查询 (Subquery) 详解

子查询是嵌套在其他SQL查询语句中的查询，也称为内部查询或嵌套查询。它是SQL中非常强大且常用的功能。

子查询的基本概念

子查询是指： - 一个SELECT语句嵌套在另一个SELECT、INSERT、UPDATE或DELETE语句中 - 可以出现在SELECT、FROM、WHERE、HAVING等子句中 - 通常用括号()括起来

子查询的类型

1. 按位置分类

• WHERE子句中的子查询：用于过滤条件

  SELECT * FROM employees 
  WHERE salary > (SELECT AVG(salary) FROM employees);
  

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

  SELECT dept_avg.dept_id, dept_avg.avg_salary
  FROM (SELECT dept_id, AVG(salary) as avg_salary 
      FROM employees GROUP BY dept_id) AS dept_avg;
  

• SELECT子句中的子查询（标量子查询）

  SELECT e.name, 
       (SELECT d.name FROM departments d WHERE d.id = e.dept_id) as dept_name
  FROM employees e;
  

2. 按返回结果分类

• 标量子查询：返回单一值
• 列子查询：返回单列多行
• 行子查询：返回单行多列
• 表子查询：返回多行多列

子查询的使用场景

1. 比较运算符中的子查询

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

2. IN/NOT IN子查询

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

3. EXISTS/NOT EXISTS子查询

SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

4. ALL/ANY/SOME子查询

SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 10);

相关子查询 vs 非相关子查询

• 非相关子查询：子查询可以独立执行，不依赖外部查询

  SELECT * FROM products
  WHERE category_id IN (SELECT id FROM categories WHERE name LIKE 'Electronics%');
  

• 相关子查询：子查询依赖外部查询的值

  SELECT e.name, e.salary
  FROM employees e
  WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
  

子查询的性能考虑

1. 相关子查询通常比非相关子查询性能差，因为需要为外部查询的每一行执行一次
2. 在可能的情况下，考虑使用JOIN替代子查询
3. 确保子查询中的列有适当的索引

实际应用示例

查找各部门中工资最高的员工

SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary = (SELECT MAX(salary) 
                  FROM employees 
                  WHERE dept_id = e.dept_id);

使用子查询实现分页

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, *
    FROM products
) AS numbered_products
WHERE row_num BETWEEN 11 AND 20;

子查询是SQL中非常灵活和强大的工具，合理使用可以解决许多复杂的数据查询问题。