Oracle 子查询（Subquery）详解

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

子查询（Subquery） 是嵌套在 SQL 语句中的查询[1]：

类型：

• 单行子查询
• 多行子查询
• 多列子查询
• 相关子查询
• 嵌套子查询

---

2. 子查询位置

2.1 WHERE 子句

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

2.2 HAVING 子句

SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

2.3 SELECT 子句

SELECT 
  name,
  salary,
  (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id) AS dept_avg
FROM employees e1;

2.4 FROM 子句（内联视图）

SELECT e.dept_id, e.avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) e
WHERE e.avg_sal > 5000;

---

3. 单行子查询

3.1 语法

SELECT * FROM employees
WHERE dept_id = (SELECT id FROM departments WHERE dept_name = 'IT');

3.2 单行操作符

操作符	说明
=	等于
<>	不等于
>	大于
>=	大于等于
<	小于
<=	小于等于

---

4. 多行子查询

4.1 语法

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

4.2 多行操作符

操作符	说明
IN	在列表中
ANY	与任一比较
ALL	与所有比较
EXISTS	存在

4.3 ANY 示例

-- 大于任一
SELECT * FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 10);

-- 等价于
SELECT * FROM employees
WHERE salary > (SELECT MIN(salary) FROM employees WHERE dept_id = 10);

4.4 ALL 示例

-- 大于所有
SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 10);

-- 等价于
SELECT * FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE dept_id = 10);

---

5. 多列子查询

5.1 成对比较

SELECT * FROM employees
WHERE (dept_id, salary) IN (
  SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id
);

5.2 非成对比较

SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'NY')
  AND salary IN (SELECT MAX(salary) FROM employees GROUP BY dept_id);

---

6. 相关子查询

6.1 语法

-- 子查询引用外层表
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary) 
  FROM employees e2 
  WHERE e2.dept_id = e.dept_id
);

6.2 EXISTS

-- 查询有员工的部门
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

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

6.3 相关子查询性能

• 每行都执行子查询
• 可优化为 JOIN

---

7. WITH 子句（CTE）

7.1 基本语法

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

7.2 多 CTE

WITH 
dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees GROUP BY dept_id
),
high_paid AS (
  SELECT * FROM employees WHERE salary > 10000
)
SELECT h.name, h.salary, d.avg_sal
FROM high_paid h
JOIN dept_avg d ON h.dept_id = d.dept_id;

7.3 递归 CTE（11g R2+）

-- 层级查询
WITH org_chart (id, name, manager_id, lvl) AS (
  -- 起点
  SELECT id, name, manager_id, 1 AS lvl
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- 递归
  SELECT e.id, e.name, e.manager_id, oc.lvl + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY lvl;

---

8. 标量子查询

8.1 SELECT 中

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

8.2 WHERE 中

SELECT * FROM employees e
WHERE e.salary = (SELECT MAX(salary) FROM employees);

8.3 性能

• 每行执行一次
• 可优化为 JOIN

---

9. 子查询优化

9.1 子查询 unnesting

-- 原始
SELECT * FROM employees e
WHERE e.dept_id IN (SELECT id FROM departments WHERE location = 'NY');

-- 优化后（自动）
SELECT e.*
FROM employees e, departments d
WHERE e.dept_id = d.id AND d.location = 'NY';

9.2 子查询合并

-- 原始
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NY')
  AND salary > (SELECT AVG(salary) FROM employees);

-- 优化后
SELECT e.*
FROM employees e
WHERE e.dept_id IN (SELECT id FROM departments WHERE location = 'NY')
  AND e.salary > (SELECT AVG(salary) FROM employees e2);

9.3 使用 WITH/CTE

-- 复杂子查询用 CTE
WITH avg_sal AS (SELECT AVG(salary) AS val FROM employees)
SELECT * FROM employees e, avg_sal a WHERE e.salary > a.val;

---

10. 子查询 vs JOIN

10.1 对比

维度	子查询	JOIN
可读性	高	中
性能	可能差	通常好
灵活性	高	中
优化	自动 unnest	直接

10.2 选择

• 简单查询：子查询
• 复杂多表：JOIN
• 复用结果：CTE

---

11. 常见坑与排错

11.1 ORA-01427: 单行子查询返回多行

修复：

-- 1. 使用 IN
WHERE dept_id IN (SELECT id FROM departments WHERE ...);

-- 2. 限制单行
WHERE dept_id = (SELECT id FROM departments WHERE ... AND ROWNUM = 1);

11.2 ORA-00904: 无效标识符

修复：

-- 检查列名
SELECT * FROM employees WHERE id = (SELECT emp_id FROM ...);
-- 子查询列名错误

11.3 相关子查询性能差

修复：

-- 1. 改写为 JOIN
SELECT e.name
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id);

-- 优化为
SELECT e.name
FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d
  ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

11.4 NOT IN 与 NULL

-- NOT IN 遇 NULL 返回空
SELECT * FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE location = 'NY');
-- 如果子查询返回 NULL，结果为空

-- 修复
SELECT * FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE location = 'NY' AND dept_id IS NOT NULL);

-- 或用 NOT EXISTS
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id AND d.location = 'NY');

---

12. 最佳实践

1. 简单查询用子查询：可读性高
2. 复杂多表用 JOIN：性能好
3. 复用结果用 CTE：清晰
4. 递归用 WITH：层级查询
5. 避免 SELECT 中标量子查询：性能差
6. NOT IN 注意 NULL：用 NOT EXISTS
7. 检查执行计划：验证优化
8. 相关子查询优化为 JOIN：提升性能
9. 使用绑定变量：减少硬解析
10. 测试大数据量：验证性能

---

13. 参考资料

[1] Oracle Database SQL Language Reference 19c, “Subqueries” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Subqueries.html

[2] Oracle Database Data Warehousing Guide 19c, “WITH Clause” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/