子查询
子查询是 指嵌套在一个上层查询中的查询。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 dept(
deptno NUMBER(2,0),
dname VARCHAR(14),
location VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);
INSERT INTO dept VALUES (20,'ACCOUNTING','Los Angeles');
INSERT INTO dept VALUES (30,'OPERATIONS','CHICAGO');
INSERT INTO dept VALUES (40,'SALES','NEW YORK');
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)
);
INSERT INTO emp VALUES (1698,'BLAKE','MANAGER',1839,'1981-5-1',2850,null,30);
INSERT INTO emp VALUES (1566,'JONES','MANAGER',1839, '1981-4-2',2975,null,40);
INSERT INTO emp VALUES (1788,'SCOTT','ANALYST',1566, '1987-7-15',3000,null,20);
INSERT INTO emp VALUES (1902,'FORD','ANALYST',1566, '1981-12-5',3000,null,40);
INSERT INTO emp VALUES (1369,'SMITH','CLERK',1902, '1980-12-17',800,null,20);
INSERT INTO emp VALUES (1499,'ALLEN','SALESMAN',1698, '1981-2-20',1600,300,30);
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 子句与标量子查询示例
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
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。
SELECT t1.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 的子查询示例
-
标量子查询
/*查询最低工资大于 30 号部门最低工资的部门编号和其最低工资*/
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 -
列子查询返回一列多行的结果集
/*返回其他职位中比 job 为 MANAGER 所有工资低的员工的员工编号、员工姓名、职位和薪水*/
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
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 -
行子查询返回一行多列的结果集
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 代替。
示例如下：
SELECT exists(SELECT empno FROM emp WHERE sal = 3000) AS 'exists返回1或者0';
+----------------------+
| exists返回1或者0 |
+----------------------+
| 1 |
+----------------------+
1 row in set
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
SELECT dname FROM dept a WHERE a.deptno IN (SELECT deptno FROM emp);
+------------+
| dname |
+------------+
| ACCOUNTING |
| OPERATIONS |
| SALES |
+------------+
3 rows in set