MySQL子查询

掌握在查询中嵌套其他查询的使用方法

全站首页 教程首页

子查询概述

子查询（嵌套查询）是指在一个查询语句中嵌套另一个查询语句。子查询可以出现在SELECT、FROM、WHERE等子句中。

子查询分类

  • 标量子查询 - 返回单个值的子查询
  • 列子查询 - 返回单列多行的子查询
  • 行子查询 - 返回单行多列的子查询
  • 表子查询 - 返回多行多列的子查询
  • 相关子查询 - 依赖于外部查询的子查询
  • 非相关子查询 - 独立于外部查询的子查询

标量子查询

标量子查询返回单个值，可以在任何使用标量值的地方使用。

标量子查询示例

SQL示例 
-- 查询工资高于平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- 查询最新订单的详细信息
SELECT *
FROM orders
WHERE order_date = (
    SELECT MAX(order_date) FROM orders
);

列子查询

列子查询返回单列多行，通常与IN、ANY、ALL等操作符一起使用。

列子查询示例

SQL示例 
-- 查询在销售部工作的员工
SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE department_name = '销售部'
);

-- 查询没有订单的客户
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

行子查询

行子查询返回单行多列，通常与行比较操作符一起使用。

行子查询示例

SQL示例 
-- 查询与张三在同一个部门且职位相同的员工
SELECT name, department, position
FROM employees
WHERE (department, position) = (
    SELECT department, position 
    FROM employees 
    WHERE name = '张三'
);

表子查询

表子查询返回多行多列，可以作为临时表在FROM子句中使用。

表子查询示例

SQL示例 
-- 查询每个部门的平均工资
SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > 5000;

-- 查询每个客户的订单数量
SELECT c.customer_name, order_count
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS oc ON c.customer_id = oc.customer_id;

相关子查询

相关子查询依赖于外部查询，对外部查询的每一行执行一次。

相关子查询示例

SQL示例 
-- 查询工资高于所在部门平均工资的员工
SELECT name, department, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- 查询每个客户的最近订单
SELECT customer_id, order_id, order_date
FROM orders o1
WHERE order_date = (
    SELECT MAX(order_date)
    FROM orders o2
    WHERE o2.customer_id = o1.customer_id
);

EXISTS和NOT EXISTS子查询

EXISTS用于检查子查询是否返回结果，NOT EXISTS用于检查子查询是否不返回结果。

EXISTS子查询示例

SQL示例 
-- 查询有订单的客户
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

-- 查询没有订单的客户
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

ANY、SOME和ALL操作符

这些操作符用于将值与子查询返回的一组值进行比较。

ANY/SOME和ALL操作符示例

SQL示例 
-- 查询工资高于任何销售部员工的员工
SELECT name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary 
    FROM employees 
    WHERE department = '销售部'
);

-- 查询工资高于所有销售部员工的员工
SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary 
    FROM employees 
    WHERE department = '销售部'
);
注意：ANY和SOME是同义词，可以互换使用。

子查询中的NULL处理

子查询结果中出现NULL时，IN和NOT IN的行为需要特别注意：

SQL示例 
-- 如果子查询包含NULL，NOT IN会返回空结果
SELECT name FROM employees WHERE department_id NOT IN (1, 2, NULL);
-- 建议使用NOT EXISTS替代
SELECT name FROM employees e WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.id = e.department_id AND d.id IN (1,2)
);

使用公用表表达式(CTE)替代子查询

MySQL 8.0+支持WITH子句（CTE），提高可读性和性能：

SQL示例 
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_sal;

子查询在ORDER BY和GROUP BY中的使用

SQL示例 
-- 按部门平均工资排序
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY (
    SELECT MAX(salary) FROM employees e2 WHERE e2.department = employees.department
) DESC;

子查询性能优化

为了提高子查询的性能，可以考虑以下优化策略：

  • 使用JOIN替代相关子查询
  • 在子查询的WHERE条件列上创建索引
  • 使用EXISTS替代IN，特别是在大表上
  • 避免在子查询中使用SELECT *
  • 考虑使用临时表存储子查询结果

使用JOIN替代子查询示例

SQL示例 
-- 使用子查询
SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '北京'
);

-- 使用JOIN替代
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.location = '北京';

子查询的限制

  • 子查询不能使用ORDER BY（除非同时使用LIMIT）
  • 某些子查询在UPDATE/DELETE中不能引用正在修改的表
  • 子查询的最大嵌套深度默认不超过64层
  • 在FROM子句中的子查询必须使用别名

子查询常见错误与注意事项

在使用子查询时，需要注意以下常见错误：

  • 子查询返回多行时使用=操作符
  • 在WHERE子句中使用ORDER BY（除非在子查询中）
  • 忘记为子查询结果指定别名
  • 在子查询中引用外部查询的列但未建立正确关联

错误示例

SQL示例 
-- 错误：子查询返回多行，不能使用=操作符
SELECT name
FROM employees
WHERE department_id = (
    SELECT id FROM departments WHERE location = '北京'
);

-- 正确：使用IN操作符
SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = '北京'
);

子查询在不同SQL语句中的应用

子查询不仅可以在SELECT语句中使用，还可以在INSERT、UPDATE和DELETE语句中使用：

在INSERT语句中使用子查询

SQL示例 
-- 将高薪员工信息插入到另一张表
INSERT INTO high_salary_employees (employee_id, name, salary)
SELECT employee_id, name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

在UPDATE语句中使用子查询

SQL示例 
-- 将部门平均工资以下的员工薪资提高10%
UPDATE employees e1
SET salary = salary * 1.1
WHERE salary < (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

在DELETE语句中使用子查询

SQL示例 
-- 删除没有订单的客户
DELETE FROM customers
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id FROM orders
);

子查询与连接查询的比较

子查询和连接查询都可以用于从多个表中检索数据，但它们各有优缺点：

特性子查询连接查询
可读性对于复杂逻辑更直观对于简单关系更直观
性能有时较慢，特别是相关子查询通常更快
灵活性可以用于更复杂的条件适用于简单的关系
维护性修改复杂逻辑可能困难通常更容易维护

递归CTE简介

对于层次结构数据，可以使用递归CTE替代复杂子查询：

SQL示例 
WITH RECURSIVE org_tree AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree;

下一步学习

掌握了子查询后，您可以继续学习：

  • 事务 - 学习如何管理数据库事务
  • 索引 - 学习如何创建和使用索引提高查询性能
  • 视图 - 了解如何创建和使用数据库视图