SQL 子查询

SQL 子查询学习笔记

子查询是嵌套在其他 SQL 语句中的 SELECT 语句，常用于从多个表中提取数据或进行复杂的条件过滤。

1. 子查询的特点

1. 子查询需要小括号：子查询必须用 () 包裹。
2. 先执行父查询：父查询先执行，然后将结果传递给子查询。
3. 子查询返回结果集：子查询的结果集会作为父查询的条件或数据源。
4. 子查询分类：
   • 单行单列
   • 单行多列
   • 多行单列
   • 多行多列

2. 子查询的常见用法

（1）单行单列子查询

子查询返回单行单列的结果，通常用于条件过滤。

示例：

-- 查询和 SCOTT 同一个职位的人
SELECT * FROM tmp_emp
WHERE job = (SELECT job FROM tmp_emp WHERE ename = 'SCOTT')
  AND ename <> 'SCOTT';

（2）单行多列子查询

子查询返回单行多列的结果，通常用于多条件过滤。

示例：

-- 查询和 SCOTT 同一个职位和同一个领导的人
SELECT * FROM tmp_emp
WHERE (job, mgr) = (SELECT job, mgr FROM tmp_emp WHERE ename = 'SCOTT')
  AND ename <> 'SCOTT';

（3）多行单列子查询

子查询返回多行单列的结果，通常用于 IN 或 NOT IN 条件。

示例：

-- 查询和 SCOTT、TURNER 同一个职位的人
SELECT * FROM tmp_emp
WHERE job IN (SELECT job FROM tmp_emp WHERE ename IN ('SCOTT', 'TURNER'))
  AND ename NOT IN ('SCOTT', 'TURNER');

（4）多行多列子查询

子查询返回多行多列的结果，通常用于多条件过滤。

示例：

-- 查询和 SCOTT、TURNER 同一个职位和同一个领导的人
SELECT * FROM tmp_emp
WHERE (job, mgr) IN (SELECT job, mgr FROM tmp_emp WHERE ename IN ('SCOTT', 'TURNER'))
  AND ename NOT IN ('SCOTT', 'TURNER');

3. 不等值子查询

不等值子查询用于比较子查询结果集中的值。

（1）ANY 子查询

• > ANY：大于子查询结果集中的任意值（大于最小值）。
• < ANY：小于子查询结果集中的任意值（小于最大值）。

示例：

-- 比任意部门最低工资还大的员工
SELECT * FROM tmp_emp
WHERE sal > ANY (SELECT MIN(sal) FROM tmp_emp GROUP BY deptno);

-- 比任意部门最高工资还小的员工
SELECT * FROM tmp_emp
WHERE sal < ANY (SELECT MAX(sal) FROM tmp_emp GROUP BY deptno);

（2）ALL 子查询

• > ALL：大于子查询结果集中的所有值（大于最大值）。
• < ALL：小于子查询结果集中的所有值（小于最小值）。

示例：

-- 比任意部门最低工资还小的员工
SELECT * FROM tmp_emp
WHERE sal < ALL (SELECT MAX(sal) FROM tmp_emp GROUP BY deptno);

4. 关联子查询

关联子查询是指子查询依赖于父查询的结果，通常用于逐行匹配。

示例：

-- 查询每个部门的最低工资的人
SELECT * FROM tmp_emp t1
WHERE sal = (SELECT MIN(sal) FROM tmp_emp t2 WHERE t2.deptno = t1.deptno);

5. EXISTS 和 NOT EXISTS

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

示例：

-- 查询公司员工是领导的人
SELECT * FROM tmp_emp t2
WHERE EXISTS (SELECT 1 FROM tmp_emp t1 WHERE t1.mgr = t2.empno);

-- 查询公司员工不是领导的人
SELECT * FROM tmp_emp t2
WHERE NOT EXISTS (SELECT 1 FROM tmp_emp t1 WHERE t1.mgr = t2.empno);

6. WITH .. AS 短语

WITH .. AS 用于创建临时表，提高查询效率和可读性。

示例：

-- 查询每个部门的最低工资的人
WITH qu AS (SELECT MIN(sal) FROM tmp_emp GROUP BY deptno)
SELECT * FROM tmp_emp WHERE sal IN (SELECT * FROM qu);

-- 使用多个临时表
WITH
    q1 AS (SELECT 3 m FROM dual),
    q2 AS (SELECT 4 n FROM dual),
    q3 AS (SELECT m + n FROM q1, q2)
SELECT * FROM q3;

7. 综合示例

以下是一些综合示例，展示了子查询的实际应用。

（1）查询 20、30 部门工资大于 1500 的人

SELECT * FROM (
    SELECT sal, deptno FROM tmp_emp WHERE deptno IN (20, 30)
) WHERE sal > 1500;

（2）查询每个部门的最低工资的人

SELECT * FROM tmp_emp
WHERE sal IN (SELECT MIN(sal) FROM tmp_emp GROUP BY deptno);

8. 总结

1. 子查询的分类：单行单列、单行多列、多行单列、多行多列。
2. 不等值子查询：ANY 和 ALL 的使用方法。
3. 关联子查询：逐行匹配的实现方式。
4. EXISTS 和 NOT EXISTS：用于检查子查询是否返回结果。
5. WITH .. AS 短语：创建临时表，提高查询效率。