子查询概述
子查询（嵌套查询）是指在一个查询语句中嵌套另一个查询语句。子查询可以出现在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是同义词，可以互换使用。
子查询性能优化
为了提高子查询的性能，可以考虑以下优化策略：
- 使用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 = '北京';子查询常见错误与注意事项
在使用子查询时，需要注意以下常见错误：
- 子查询返回多行时使用=操作符
- 在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
);子查询与连接查询的比较
子查询和连接查询都可以用于从多个表中检索数据，但它们各有优缺点：
| 特性 | 子查询 | 连接查询 |
|---|---|---|
| 可读性 | 对于复杂逻辑更直观 | 对于简单关系更直观 |
| 性能 | 有时较慢，特别是相关子查询 | 通常更快 |
| 灵活性 | 可以用于更复杂的条件 | 适用于简单的关系 |
| 维护性 | 修改复杂逻辑可能困难 | 通常更容易维护 |
下一步学习
掌握了子查询后，您可以继续学习：