单行子查询
子查询返回一行一列时，可以用 = < > 等标量运算符。如果它意外返回了多行，会报 ORA-01427。
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;在你的专用沙箱里执行打开演练场
SELECT d.department_name,
COUNT(*) AS headcount,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees)
ORDER BY avg_salary DESC;在你的专用沙箱里执行打开演练场
多行子查询：IN / ANY / ALL
- = ANY 等价于 IN；<> ALL 等价于 NOT IN
- NOT IN 列表里若含 NULL，整个条件永远不成立（返回空结果）
- 这个 NULL 陷阱是实战中最隐蔽的坑之一，宁可改用 NOT EXISTS
-- IN：等于列表中的任意一个
SELECT last_name, salary FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
-- ANY：只需满足其中一个比较（> ANY 意为「大于最小值」）
SELECT last_name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');
-- ALL：必须满足所有比较（> ALL 意为「大于最大值」）
SELECT last_name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');在你的专用沙箱里执行打开演练场
EXISTS 与相关子查询
相关子查询会引用外层的列，因此对每一行都要执行一次。EXISTS 只关心「有没有匹配行」，找到一条就可以停止，通常比 IN 更高效且不受 NULL 影响。
-- 查出至少有一名员工工资超过 15000 的部门
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.department_id
AND e.salary > 15000
)
ORDER BY d.department_id;
-- 反查：没有任何员工的部门（等价于上一章的 LEFT JOIN ... IS NULL）
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);在你的专用沙箱里执行打开演练场
WITH 子句（CTE）：把复杂查询拆开
- WITH 只是把子查询提到前面，逻辑上等价于内联视图，但可读性大幅提升
- 同一个 CTE 可以被引用多次；优化器可能把它并入外层查询，也可能物化成临时表，可用 MATERIALIZE / INLINE 提示干预
- 11gR2 起支持递归 WITH（WITH ... AS (… UNION ALL …)），用于树形结构；12.2 起递归成员可并行
WITH dept_stats AS (
SELECT department_id,
COUNT(*) AS headcount,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
),
company_avg AS (
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees
)
SELECT d.department_name,
s.headcount,
s.avg_salary,
c.avg_salary AS company_avg
FROM dept_stats s
JOIN departments d ON d.department_id = s.department_id
CROSS JOIN company_avg c
WHERE s.avg_salary > c.avg_salary
ORDER BY s.avg_salary DESC;在你的专用沙箱里执行打开演练场