SQL语言 - 子查询

SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。

SQL语言 - 子查询

概述

语法： SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2); 子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）

根据子查询位置可分为：

WHERE 之后
FROM 之后
SELECT 之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。常用操作符：- < > > >= < <=

例子：

-- 查询 "销售部" 所有员工信息
select id from dept where name = '销售部';
-- 根据销售部部门ID，查询员工信息
select * from emp where dept_id = 4;
-- 合并（子查询）
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 查询在 "方东白" 入职之后的员工信息
select entrydate from emp where name = '方东白';
-- 根据入职日期查询之后入职的员工信息
select * from emp where entrydate > '2009-02-12';
-- 合并（子查询）
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

1
2
3
4
5
6
7
8
9
10
11
12
13

查询结果:

mysql> select id from dept where name = '销售部';
+----+
| id |
+----+
|  4 |
+----+
1 row in set (0.00 sec)

mysql> select * from emp where dept_id = 4;
+----+-----------+------+--------------+--------+------------+-----------+---------+
| id | name      | age  | job          | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------------+--------+------------+-----------+---------+
| 14 | 张三丰    |   88 | 销售总监     |  14000 | 2004-10-12 |         1 |       4 |
| 15 | 俞莲舟    |   38 | 销售         |   4600 | 2004-10-12 |        14 |       4 |
| 16 | 宋远桥    |   40 | 销售         |   4600 | 2004-10-12 |        14 |       4 |
+----+-----------+------+--------------+--------+------------+-----------+---------+
3 rows in set (0.00 sec)

mysql> select * from emp where dept_id = (select id from dept where name = '销售部');
+----+-----------+------+--------------+--------+------------+-----------+---------+
| id | name      | age  | job          | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------------+--------+------------+-----------+---------+
| 14 | 张三丰    |   88 | 销售总监     |  14000 | 2004-10-12 |         1 |       4 |
| 15 | 俞莲舟    |   38 | 销售         |   4600 | 2004-10-12 |        14 |       4 |
| 16 | 宋远桥    |   40 | 销售         |   4600 | 2004-10-12 |        14 |       4 |
+----+-----------+------+--------------+--------+------------+-----------+---------+
3 rows in set (0.00 sec)


mysql> select entrydate from emp where name = '方东白';
+------------+
| entrydate  |
+------------+
| 2009-02-12 |
+------------+
1 row in set (0.00 sec)

mysql> select * from emp where entrydate > '2009-02-12';
+----+-----------+------+--------+--------+------------+-----------+---------+
| id | name      | age  | job    | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------+--------+------------+-----------+---------+
|  9 | 丁敏君    |   23 | 出纳   |   5250 | 2009-05-13 |         7 |       3 |
| 17 | 陈友谅    |   42 | NULL   |   2000 | 2011-10-12 |         1 |    NULL |
+----+-----------+------+--------+--------+------------+-----------+---------+
2 rows in set (0.01 sec)

mysql> select * from emp where entrydate > (select entrydate from emp where name = '方东白');
+----+-----------+------+--------+--------+------------+-----------+---------+
| id | name      | age  | job    | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------+--------+------------+-----------+---------+
|  9 | 丁敏君    |   23 | 出纳   |   5250 | 2009-05-13 |         7 |       3 |
| 17 | 陈友谅    |   42 | NULL   |   2000 | 2011-10-12 |         1 |    NULL |
+----+-----------+------+--------+--------+------------+-----------+---------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用操作符：

操作符	描述
IN	在指定的集合范围内，多选一
NOT IN	不在指定的集合范围内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

例子：

-- 查询销售部和市场部的所有员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
-- 查询比财务部所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

1
2
3
4
5
6

查询结果:

mysql> select * from emp where dept_id in (select id from dept where name = '销售部' or name = ' 市场部');
+----+-----------+------+-----------------+--------+------------+-----------+---------+
| id | name      | age  | job             | salary | entrydate  | managerid | dept_id |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
| 10 | 赵敏      |   20 | 市场部总监      |  12500 | 2004-10-12 |         1 |       2 |
| 11 | 鹿杖客    |   56 | 职员            |   3750 | 2006-10-03 |        10 |       2 |
| 12 | 鹤笔翁    |   19 | 职员            |   3750 | 2007-05-09 |        10 |       2 |
| 13 | 方东白    |   19 | 职员            |   5500 | 2009-02-12 |        10 |       2 |
| 14 | 张三丰    |   88 | 销售总监        |  14000 | 2004-10-12 |         1 |       4 |
| 15 | 俞莲舟    |   38 | 销售            |   4600 | 2004-10-12 |        14 |       4 |
| 16 | 宋远桥    |   40 | 销售            |   4600 | 2004-10-12 |        14 |       4 |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
7 rows in set (0.00 sec)

mysql>
mysql> select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部'));
Empty set (0.00 sec)

mysql> select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
+----+-----------+------+-----------------+--------+------------+-----------+---------+
| id | name      | age  | job             | salary | entrydate  | managerid | dept_id |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
|  1 | 金庸      |   66 | 总裁            |  20000 | 2000-01-01 |      NULL |       5 |
|  2 | 张无忌    |   20 | 项目经理        |  12500 | 2005-12-05 |         1 |       1 |
|  3 | 杨逍      |   33 | 开发            |   8400 | 2000-11-03 |         2 |       1 |
|  4 | 韦一笑    |   48 | 开发            |  11000 | 2002-02-05 |         2 |       1 |
|  5 | 常遇春    |   43 | 开发            |  10500 | 2004-09-07 |         3 |       1 |
|  7 | 灭绝      |   60 | 财务总监        |   8500 | 2002-09-12 |         1 |       3 |
|  8 | 周芷若    |   19 | 会计            |  48000 | 2006-06-02 |         7 |       3 |
| 10 | 赵敏      |   20 | 市场部总监      |  12500 | 2004-10-12 |         1 |       2 |
| 14 | 张三丰    |   88 | 销售总监        |  14000 | 2004-10-12 |         1 |       4 |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
9 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用操作符：=, <, >, IN, NOT IN

例子：

-- 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

1
2

查询结果:

mysql> select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
+----+-----------+------+-----------------+--------+------------+-----------+---------+
| id | name      | age  | job             | salary | entrydate  | managerid | dept_id |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
|  2 | 张无忌    |   20 | 项目经理        |  12500 | 2005-12-05 |         1 |       1 |
| 10 | 赵敏      |   20 | 市场部总监      |  12500 | 2004-10-12 |         1 |       2 |
+----+-----------+------+-----------------+--------+------------+-----------+---------+

1
2
3
4
5
6

表子查询

返回的结果是多行多列常用操作符：IN

例子：

-- 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
-- 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

1
2
3
4

查询结果:

mysql> select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
+----+-----------+------+--------+--------+------------+-----------+---------+
| id | name      | age  | job    | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------+--------+------------+-----------+---------+
| 11 | 鹿杖客    |   56 | 职员   |   3750 | 2006-10-03 |        10 |       2 |
| 12 | 鹤笔翁    |   19 | 职员   |   3750 | 2007-05-09 |        10 |       2 |
| 15 | 俞莲舟    |   38 | 销售   |   4600 | 2004-10-12 |        14 |       4 |
| 16 | 宋远桥    |   40 | 销售   |   4600 | 2004-10-12 |        14 |       4 |
+----+-----------+------+--------+--------+------------+-----------+---------+
4 rows in set (0.00 sec)

mysql> select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
+----+-----------+------+--------+--------+------------+-----------+---------+------+-----------+
| id | name      | age  | job    | salary | entrydate  | managerid | dept_id | id   | name      |
+----+-----------+------+--------+--------+------------+-----------+---------+------+-----------+
|  8 | 周芷若    |   19 | 会计   |  48000 | 2006-06-02 |         7 |       3 |    3 | 财务部    |
|  9 | 丁敏君    |   23 | 出纳   |   5250 | 2009-05-13 |         7 |       3 |    3 | 财务部    |
| 11 | 鹿杖客    |   56 | 职员   |   3750 | 2006-10-03 |        10 |       2 |    2 | 市场部    |
| 12 | 鹤笔翁    |   19 | 职员   |   3750 | 2007-05-09 |        10 |       2 |    2 | 市场部    |
| 13 | 方东白    |   19 | 职员   |   5500 | 2009-02-12 |        10 |       2 |    2 | 市场部    |
| 17 | 陈友谅    |   42 | NULL   |   2000 | 2011-10-12 |         1 |    NULL | NULL | NULL      |
+----+-----------+------+--------+--------+------------+-----------+---------+------+-----------+
6 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

← SQL语言 - 连接查询 SQL语言 - 多表查询 →