SQL语言 - 子查询
SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。
概述
语法： 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 = '方东白');
查询结果:
详情
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 |
+----+-----------+------+--------+--------+------------+-----------+---------+
列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用操作符：
| 操作符 | 描述 |
|---|---|
| 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 = '研发部') );
查询结果:
详情
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)
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用操作符：=, <, >, IN, NOT IN
例子：
-- 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
查询结果:
详情
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 |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
表子查询
返回的结果是多行多列 常用操作符：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 ;
查询结果:
详情
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)