子查询

子查询是指嵌套在一个上层查询中的查询。SQL 语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。

子查询简介

子查询可以出现在 SQL 语句中的各种子句中，例如 SELECT 子句、FROM 子句、WHERE 子句等。SELECT 语句的 FROM 子句中的子查询也称为内联视图。您可以在内联视图中嵌套任意数量的子查询。SELECT 语句的 WHERE 子句中的子查询也称为嵌套子查询。

子查询类别

根据结果集的行列数不同，可以分为如下四类子查询：

子查询应用场景

子查询主要用于以下场景：

• 在 INSERT 或 CREATE TABLE 语句中定义要插入到的目标表中的行集。

• 在 CREATE VIEW 语句中定义要包含在视图中的行集。

• 在 UPDATE 语句中定义要分配给现有行的一个或多个值。

• 为 SELECT、UPDATE 和 DELETE 语句的 WHERE 子句、HAVING 子句或 START WITH 子句中的条件提供值。

子查询关键词

子查询中的关键词包括 IN、ANY、SOME 和 ALL 等。

• IN 常用于 WHERE 表达式中，用于查询某个范围内的数据。

• ANY 和 SOME 可以与 =、>、>=、<、<=、<> 操作符结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

• ALL 可以与 =、>、>=、<、<=、<> 操作符结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。

注意

使用 NOT IN 时，如果子查询中列的值为 NULL，外查询的结果为空。

子查询示例

创建示例表 emp 和 dept，并插入适当数据。

CREATE TABLE emp(  
    empno         NUMBER(4,0),  
    empname       VARCHAR(10),  
    job           VARCHAR(9),  
    mgr           NUMBER(4,0),  
    hiredate      DATE,  
    sal           NUMBER(7,2),  
    comm          NUMBER(7,2),        
    deptno        NUMBER(2,0),   
    CONSTRAINT PK_emp PRIMARY KEY (empno), 
    CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
);

CREATE TABLE dept(  
    deptno           NUMBER(2,0),  
    dname            VARCHAR(14),  
    location         VARCHAR(13),   
    CONSTRAINT pk_dept PRIMARY KEY(deptno)  
);

CREATE TABLE job_grades
(
     grade_level VARCHAR(5),
     lowest_sal INT,
     highest_sal INT);

INSERT INTO job_grades VALUE ('A', 1000, 1999);
INSERT INTO job_grades VALUE ('B', 2000, 2999);
INSERT INTO job_grades VALUE ('C', 3000, 3999);
INSERT INTO job_grades VALUE ('D', 40000, 4999);
INSERT INTO job_grades VALUE ('E', 5000, 5999);

SELECT 子句与标量子查询示例

obclient> SELECT a.*,(SELECT count(*) FROM emp b WHERE b.deptno = a.deptno) AS 员工数量 FROM dept a;
+--------+------------+-------------+--------------+
| deptno | dname      | location    | 员工数量     |
+--------+------------+-------------+--------------+
|     20 | ACCOUNTING | Los Angeles |            2 |
|     30 | OPERATIONS | CHICAGO     |            2 |
|     40 | SALES      | NEW YORK    |            2 |
+--------+------------+-------------+--------------+
3 rows in set

obclient> SELECT (SELECT a.dname FROM dept a, emp b WHERE a.deptno = b.deptno AND b.empno = 1566) AS 部门名称;
+--------------+
| 部门名称     |
+--------------+
| SALES        |
+--------------+
1 row in set

FROM 子句与表子查询示例

将子查询的结果集作为一张表，必须为其起别名，例如下面示例中的表别名为 t1 和 t2。

obclient> SELECTt1.deptno,sa AS '平均工资', t2.grade_level FROM (SELECT deptno,avg(a.sal) sa FROM emp a GROUP BY a.deptno) t1, job_grades t2
    WHERE t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;
+--------+--------------+-------------+
| deptno | 平均工资     | grade_level |
+--------+--------------+-------------+
|     20 |  1900.000000 | A           |
|     30 |  2225.000000 | B           |
|     40 |  2987.500000 | B           |
+--------+--------------+-------------+
3 rows in set

带有 WHERE 和 HAVING 的子查询示例

示例 1：标量子查询

/*查询最低工资大于 30 号部门最低工资的部门编号和其最低工资*/
obclient> SELECT min(a.sal) minsalary,deptno FROM emp a GROUP BY a.deptno HAVING min(a.sal) > (SELECT min(sal) FROM emp WHERE deptno = 30);
+-----------+--------+
| minsalary | deptno |
+-----------+--------+
|   2975.00 |     40 |
+-----------+--------+
1 row in set

示例 2：列子查询返回一列多行的结果集

/*返回其他职位中比 job 为 MANAGER 所有工资低的员工的员工编号、员工姓名、职位和薪水*/

obclient> SELECT empname 员工姓名, empno 员工编号, job 职位, sal 薪水 FROM emp WHERE sal < ALL (SELECT DISTINCT sal
   FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
+--------------+--------------+----------+---------+
| 员工姓名     | 员工编号     | 职位     | 薪水    |
+--------------+--------------+----------+---------+
| SMITH        |         1369 | CLERK    |  800.00 |
| ALLEN        |         1499 | SALESMAN | 1600.00 |
+--------------+--------------+----------+---------+
2 rows in set

obclient> SELECT empname 员工姓名, empno 员工编号, job 职位, sal 薪水 FROM emp WHERE sal < ALL (SELECT min(sal)
    FROM emp WHERE job = 'MANAGER') AND job!= 'MANAGER';
+--------------+--------------+----------+---------+
| 员工姓名      | 员工编号      | 职位     | 薪水    |
+--------------+--------------+----------+---------+
| SMITH        |         1369 | CLERK    |  800.00 |
| ALLEN        |         1499 | SALESMAN | 1600.00 |
+--------------+--------------+----------+---------+
2 rows in set

示例 3：行子查询返回一行多列的结果集

obclient> SELECT * FROM emp a WHERE a.empno = (SELECT max(empno) FROM emp) AND sal = (SELECT max(sal) FROM emp);
+-------+---------+---------+------+------------+---------+------+--------+
| empno | empname | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+---------+---------+------+------------+---------+------+--------+
|  1902 | FORD    | ANALYST | 1566 | 1981-12-05 | 3000.00 | NULL |     40 |
+-------+---------+---------+------+------------+---------+------+--------+
1 row in set

EXISTS 与相关子查询

EXISTS 子查询先执行主查询，得到主查询的结果，再根据子查询进行过滤，子查询中 涉及到主查询中用到的字段，所以叫相关子查询。EXISTS 查询结果为 1 或 0，用来判断子查询的结果集中是否有值。一般来说，EXISTS 子查询都能用 IN 代替。

示例如下：

obclient> SELECT exists(SELECT empno FROM emp WHERE sal = 3000) AS 'exists返回1或者0';
+----------------------+
| exists返回1或者0     |
+----------------------+
|                    1 |
+----------------------+
1 row in set

obclient> SELECT dname FROM dept a WHERE exists(SELECT 1 FROM emp b WHERE a.deptno = b.deptno);
+------------+
| dname      |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES      |
+------------+
3 rows in set

obclient> SELECT dname FROM dept a WHERE a.deptno IN (SELECT deptno FROM emp);
+------------+
| dname      |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES      |
+------------+
3 rows in set