SQL 子查询详解

子查询（Subquery）是嵌套在另一个SQL查询中的查询语句，也称为内部查询或嵌套查询。子查询可以出现在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. 按返回结果分类

• 标量子查询：返回单个值（一行一列）

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

• 列子查询：返回单列多行

  SELECT * FROM employees 
  WHERE dept_id IN (SELECT id FROM departments WHERE location = 'New York');

• 行子查询：返回单行多列

  SELECT * FROM employees 
  WHERE (salary, bonus) = (SELECT MAX(salary), MAX(bonus) FROM employees);

• 表子查询：返回多行多列（通常用于FROM子句）

  SELECT * FROM 
    (SELECT dept_id, COUNT(*) AS emp_count FROM employees GROUP BY dept_id) AS dept_stats
  WHERE emp_count > 10;

子查询与操作符

1. IN/NOT IN 操作符

-- 查找在特定部门的员工
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE name IN ('IT', 'Finance'));

2. EXISTS/NOT EXISTS 操作符

-- 查找有订单的客户
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

3. 比较操作符（=, >, <, >=, <=, <>）

-- 查找工资高于部门平均工资的员工
SELECT e.* FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

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

• 非相关子查询：内部查询独立于外部查询

  SELECT * FROM products
  WHERE category_id = (SELECT id FROM categories WHERE name = 'Electronics');

• 相关子查询：内部查询引用外部查询的列

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

子查询优化技巧

1. 使用JOIN替代子查询（某些情况下性能更好）

   -- 子查询方式
   SELECT * FROM employees 
   WHERE dept_id IN (SELECT id FROM departments WHERE location = 'New York');
   
   -- JOIN方式
   SELECT e.* FROM employees e
   JOIN departments d ON e.dept_id = d.id
   WHERE d.location = 'New York';

2. 使用EXISTS代替IN（当只需要判断存在性时）

   -- 低效的IN
   SELECT * FROM customers
   WHERE id IN (SELECT customer_id FROM orders);
   
   -- 更高效的EXISTS
   SELECT * FROM customers c
   WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

3. 避免在SELECT子句中使用相关子查询（可能导致性能问题）

4. 考虑使用临时表或CTE（复杂子查询时）

   -- 使用CTE (MySQL 8.0+)
   WITH dept_stats AS (
     SELECT dept_id, AVG(salary) AS avg_salary
     FROM employees
     GROUP BY dept_id
   )
   SELECT e.* FROM employees e
   JOIN dept_stats ds ON e.dept_id = ds.dept_id
   WHERE e.salary > ds.avg_salary;

子查询的限制

1. 某些数据库对子查询的嵌套层数有限制

2. 子查询通常比等效的JOIN操作性能差

3. 在UPDATE/DELETE语句中使用子查询可能有额外限制

MySQL中的子查询优化

MySQL 5.6+对子查询进行了多项优化：

• 将某些IN子查询转换为半连接(semi-join)

• 子查询物化(Materialization)

• EXISTS-to-IN转换

可以通过EXPLAIN查看子查询的执行计划：

EXPLAIN SELECT * FROM employees 
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'New York');

子查询是SQL中强大的工具，合理使用可以简化复杂查询，但需要注意性能影响。