Oracle SQL 子查询与 EXISTS 详解

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

---

1. 概述

子查询与 EXISTS 用法详解[1]：

详细见：Oracle 子查询与 EXISTS。

---

2. 子查询类型

2.1 单行

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

2.2 多行

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

2.3 多列

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

2.4 相关

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

2.5 派生表

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

---

3. IN

3.1 基本

SELECT * FROM employees
WHERE dept_id IN (10, 20, 30);

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

3.2 NOT IN

-- 注意 NULL！
SELECT * FROM employees
WHERE dept_id NOT IN (SELECT id FROM departments WHERE id IS NOT NULL);

3.3 NULL 问题

-- 若子查询有 NULL，NOT IN 返回空
SELECT * FROM employees
WHERE dept_id NOT IN (SELECT id FROM departments);
-- 若 departments.id 有 NULL，结果为空

-- 安全：NOT EXISTS
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

---

4. EXISTS

4.1 基本

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

4.2 NOT EXISTS

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

4.3 相关

SELECT e.name
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM projects p, emp_projects ep
  WHERE ep.emp_id = e.id AND ep.project_id = p.id
    AND p.status = 'ACTIVE'
);

---

5. IN vs EXISTS

5.1 选择

- IN：子查询小，外查询大
- EXISTS：外查询小，子查询大

5.2 IN 示例

-- 子查询小
SELECT * FROM big_employees
WHERE dept_id IN (SELECT id FROM small_departments WHERE ...);

5.3 EXISTS 示例

-- 外查询小
SELECT * FROM small_employees e
WHERE EXISTS (SELECT 1 FROM big_departments d WHERE d.id = e.dept_id AND ...);

5.4 性能

- CBO 通常自动选择
- 测试比较

---

6. ANY / ALL

6.1 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);

6.2 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);

---

7. 标量子查询

7.1 SELECT

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

7.2 WHERE

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

7.3 性能

- 每行执行
- 慎用
- JOIN 替代

---

8. CTE 替代

8.1 子查询

-- 派生表
SELECT * FROM (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
) d
WHERE d.avg_sal > 5000;

8.2 CTE

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_sal > 5000;

详细见：Oracle CTE 与递归查询。

---

9. 应用场景

9.1 比较平均

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

9.2 Top N

SELECT * FROM employees e
WHERE 3 > (
  SELECT COUNT(*) FROM employees e2 
  WHERE e2.dept_id = e.dept_id AND e2.salary > e.salary
);
-- 每部门 Top 3

9.3 存在检查

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

9.4 不存在

-- 未下单客户
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

9.5 差集

-- 表 A 有但表 B 没有
SELECT * FROM a
WHERE id NOT IN (SELECT id FROM b WHERE id IS NOT NULL);

-- 或
SELECT * FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);

---

10. 性能

10.1 索引

- 子查询连接列索引
- 相关子查询性能

10.2 重写

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

-- JOIN
SELECT DISTINCT e.* FROM employees e, departments d
WHERE e.dept_id = d.id AND d.location = 'NY';

10.3 CTE

- 可读性
- 重用
- 优化

详细见：Oracle SQL 查询优化技巧。

---

11. NULL 处理

11.1 NOT IN

-- 危险
SELECT * FROM t WHERE x NOT IN (SELECT y FROM t2);
-- 若 t2.y 有 NULL，结果为空

-- 安全
SELECT * FROM t WHERE x NOT IN (SELECT y FROM t2 WHERE y IS NOT NULL);

-- 推荐
SELECT * FROM t WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.y = t.x);

11.2 EXISTS

- NULL 安全
- 推荐

---

12. 常见坑与排错

12.1 NOT IN NULL

- 子查询 NULL
- 结果空
- NOT EXISTS

12.2 相关子查询慢

- 每行执行
- 索引
- JOIN 替代

12.3 多列

- IN 多列
- EXISTS 替代

---

13. 最佳实践

1. EXISTS 优先：NULL 安全
2. IN 小子查询：性能
3. CTE：清晰
4. JOIN 替代：性能
5. 索引连接列：性能
6. NULL 处理：谨慎
7. 测试：性能
8. 执行计划：验证
9. 简单：可读
10. 文档：说明

---

14. 参考资料

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