文章来源:https://blog.csdn.net/horses/article/details/107984183
原文作者:不剪发的Tony老师
来源平台:CSDN
![ec85471e6845156df962b1ad7c35f72b.png](https://i-blog.csdnimg.cn/blog_migrate/ac49d7993059d3f16fec7afeb77be761.jpeg)
19.1 子查询概述
子查询(Subquery)是指嵌套在其他 SQL 语句( SELECT、INSERT、UPDATE、DELETE 等)中的查询语句。子查询也称为内查询(inner query),必须位于括号之中;包含子查询的查询也称为外查询(outer query)。子查询支持多层嵌套,也就是子查询中包含其他子查询。
例如,以下语句返回了月薪大于平均月薪的员工:
select emp_name, salaryfrom employeewhere salary > ( select avg(salary) from employee );
其中,括号内部的子查询用于获得员工的平均月薪(9832.00);外查询用于返回月薪大于平均月薪的员工信息。该查询的结果如下:
emp_name |salary |----------|--------|刘备 |30000.00|关羽 |26000.00|张飞 |24000.00|诸葛亮 |24000.00|孙尚香 |12000.00|赵云 |15000.00|法正 |10000.00|
MySQL 中的子查询可以分为以下三种类型:
- 标量子查询(Scalar Subquery):返回单个值(一行一列)的子查询。上面的示例就是一个标量子查询。
- 行子查询(Row Subquery):返回单行结果(一行多列)的子查询,标量子查询是行子查询的一个特例。
- 表子查询(Table Subquery):返回一个虚拟表(多行多列)的子查询,行子查询是表子查询的一个特例。
19.2 标量子查询
标量子查询的结果就像一个常量一样,可以用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。对于上面的子查询示例,实际相当于先执行以下语句得到平均月薪:
select avg(salary)from employee;avg(salary)|-----------|9832.000000|
然后将该值替换到外查询中:
select emp_name, salaryfrom employeewhere salary > ( 9832 );
19.3 行子查询
行子查询可以当作一个一行多列的临时表使用。以下语句查找所有与“黄忠”在同一个部门并且职位相同的员工:
select emp_name, dept_id, job_idfrom employeewhere (dept_id, job_id) = (select dept_id, job_id from employee where emp_name = '黄忠')and emp_name != '黄忠';emp_name|dept_id|job_id|--------|-------|------|魏延 | 2| 4|
子查询返回了“黄忠”所在的部门编号和职位编号,这两个数值构成了一行数据;外部查询的 WHERE 条件使用该数据行进行过滤,AND 操作符用于排除“黄忠”自己。
行子查询可以使用以下比较运算符:=、>、=、<=、<>、!=、<=>。如果行子查询产生多行记录将会返回错误,因为这些运算符只能和单个记录进行比较。
19.4 表子查询
当子查询返回的结果包含多行数据时,称为表子查询。表子查询通常用于 FROM 子句或者查询条件中。
19.4.1 派生表
当子查询出现在 FROM 子句中时,相当于创建了一个语句级别的临时表或者视图,也被称为派生表(derived table)。例如:
select d.dept_name as "部门名称", coalesce(de.emp_number,0) as "员工数量"from department dleft join (select dept_id, count(*) as emp_number from employee group by dept_id) deon (d.dept_id = de.dept_id);部门名称 |员工数量|--------|-------|行政管理部| 3|人力资源部| 3|财务部 | 2|研发部 | 9|销售部 | 8|保卫部 | 0|
其中,left join 后面是一个派生表(必须指定别名,这里是 de),它包含了各个部门的编号和员工数量;然后将 department 与 de 进行左外连接查询,返回了部门信息和对应的员工数量。
19.4.2 IN 操作符
当 WHERE 条件中的子查询返回多行数据时,不能再使用普通的比较运算符,因为它们不支持单个值和多个值的比较;如果想要判断某个字段是否在子查询返回的数据列表中,可以使用 IN 操作符。例如:
select emp_namefrom employeewhere job_id in (select job_id from job);
子查询返回了所有的职位编号,in 操作符用于返回 job_id 等于其中任何一个编号的员工,因此结果会返回所有的员工。该语句等价于以下语句:
select emp_namefrom employeewhere job_id = 1or job_id = 2...or job_id = 10;
NOT IN 操作符执行和 IN 相反的操作,也就是当表达式不等于任何子查询返回结果时为 True。
19.4.3 ALL、ANY/SOME 操作符
除了 IN 运算符之外,ALL、ANY/SOME 运算符与比较运算符的结合也可以用于判断子查询的返回结果:
operand comparison_operator ALL (subquery)operand comparison_operator ANY (subquery)operand comparison_operator SOME (subquery)
其中,comparison_operator 是比较运算符,包括 =、>、=、<=、<>、!=。
ALL 和比较运算符一起使用表示将表达式和子查询的结果进行比较,如果比较的结果都为 True 时最终结果就为 True。例如:
select emp_name, salaryfrom employeewhere salary > all (select e.salary from employee e join department d on (d.dept_id = e.dept_id) where d.dept_name = '研发部');emp_name|salary |--------|--------|刘备 |30000.00|关羽 |26000.00|张飞 |24000.00|诸葛亮 |25000.00|
其中,子查询返回了研发部所有员工的月薪;“> all”表示大于子查询结果中的所有值,也就是大于子查询结果中的最大值(15000)。
对于 ALL 操作符,有两个需要注意的情况,就是子查询结果为空或者存在 NULL 值。例如:
select emp_name, salaryfrom employeewhere salary > all (select 999999 from dual where 1=0);
以上查询会返回所有的员工,因为子查询返回结果为空集,外查询相当于没有 where 条件。
以下查询不会返回任何结果:
select emp_name, salaryfrom employeewhere salary > all (select max(999999) from dual where 1=0);
由于子查询返回一行数据 NULL,任何数值和 NULL 比较的结果都是未知(unknown ),所以外查询返回空集。
ANY/SOME 和比较运算符一起使用表示将表达式和子查询的结果进行比较,如果任何比较的结果为 True,最终结果就为 True。例如:
select emp_namefrom employeewhere job_id = any (select job_id from job);
该语句等价于上面的 IN 操作符示例,也就是说 = ANY 和 IN 操作符等价。
另外,需要注意的是 NOT IN 等价于 <> ALL,而不是 <> ANY。因为“a not in (1,2,3)”和“a <> all (1,2,3)”等价于:
a <> 1 and a <> 2 and a <>3
“a <> any (1,2,3)”等价于:
a <> 1 or a <> 2 or a <>3
19.5 关联子查询
在上面的示例中,子查询和外查询之间没有联系,可以单独运行。这种子查询也称为非关联子查询(Non-correlated Subquery)。另一类子查询会引用外查询中的字段,从而与外部查询产生关联,也称为关联子查询(Correlated Subquery)。
以下示例通过使用关联子查询获得各个部门的员工数量:
select d.dept_name as "部门名称", (select count(*) from employee where dept_id = d.dept_id) as "员工数量"from department d;部门名称 |员工数量|--------|-------|行政管理部| 3|人力资源部| 3|财务部 | 2|研发部 | 9|销售部 | 8|保卫部 | 0|
其中,子查询的 where 条件中使用了外查询的部门编号(d.dept_id),从而与外查询产生关联。该语句执行时,外查询先检索出所有的部门数据,针对每条记录再将 d.dept_id 传递给子查询;子查询返回每个部门的员工数量。该查询的结果与 19.4.1 中的派生表示例相同。
19.6 EXISTS 操作符
EXISTS 操作符用于判断子查询结果的存在性。如果子查询存在任何结果,EXISTS 返回 True;否则,返回 False。
例如,以下语句返回了存在女性员工的部门:
select d.dept_namefrom department dwhere exists ( select 1 from employee e where e.sex = '女' and e.dept_id = d.dept_id );dept_name|---------|财务部 |研发部 |
其中,exists 之后是一个关联子查询,先执行外查询找到 d.dept_id;然后依次将 d.dept_id 传递给子查询,判断该部门是否存在女性员工,如果存在则返回部门信息。
EXISTS 只判断结果的存在性,因此子查询的 SELECT 列表中的内容无所谓,通常使用一个常量值。EXISTS 只要找到任何数据,立即终止子查询的执行,因此可以提高查询的性能。
NOT EXISTS 执行相反的操作。如果想要查找不存在女性员工的部门,可以将上例中的 EXISTS 替换成 NOT EXISTS。
[NOT] EXISTS 和 [NOT] IN 都可以用于判断子查询返回的结果,但是它们之间存在一个重要的区别:[NOT] EXISTS 只检查存在性,[NOT] IN 需要比较实际的值是否相等。因此,当子查询的结果包含 NULL 值时,EXISTS 仍然返回结果,NOT EXISTS 不返回结果;但是此时 IN 和 NOT IN 都不会返回结果,因为 (X = NULL) 和 NOT (X = NULL) 的结果都是未知。
以下示例演示了这两者之间的区别:
select d.dept_namefrom department dwhere not exists ( select null from employee e where e.dept_id = d.dept_id );dept_name|---------|保卫部 |select d.dept_namefrom department dwhere d.dept_id not in ( select null from employee e );dept_name|---------|
第一个查询使用了 NOT EXISTS,子查询中除了“保卫部”之外的部门都有返回结果(NULL 也是结果),所以外查询只返回“保卫部”。第二个查询使用了 NOT IN,子查询中返回的都是 NULL 值;d.dept_id = NULL 的结果是未知,加上 NOT 之后仍然未知,所以查询没有返回任何结果。
EXISTS 和 IN 操作符返回左表(外查询)中与右表(子查询)至少匹配一次的数据行,实际上是一种半连接(Semi-join);NOT EXISTS 或者 NOT IN 操作符返回左表(外查询)中与右表(子查询)不匹配的数据行,实际上是一种反连接(Anti-join)。
19.7 横向派生表
对于派生表而言,它必须能够单独运行,而不能依赖其他表。例如,以下语句想要返回每个部门内月薪最高的员工:
select d.dept_name, t.emp_name, t.salaryfrom department dleft join (select e.dept_id, e.emp_name, e.salary from employee e where e.dept_id = d.dept_id order by e.salary desc limit 1 ) t on d.dept_id = t.dept_id;RROR 1054 (42S22): Unknown column 'd.dept_id' in 'where clause'
该语句失败的原因在于子查询 t 不能引用外查询中的 department 表。
从 MySQL 8.0.14 开始,派生表支持 LATERAL 关键字前缀,表示允许派生表引用它所在的 FROM 子句中的其他表。这种派生表被称为横向派生表(Lateral Derived Table)。
对于上面的问题,可以使用 LATERAL 派生表实现:
select d.dept_name, t.emp_name, t.salaryfrom department dleft join lateral (select e.dept_id, e.emp_name, e.salary from employee e where e.dept_id = d.dept_id order by e.salary desc limit 1 ) t on d.dept_id = t.dept_id;dept_name |emp_name|salary |------------|--------|--------|行政管理部 |刘备 |30000.00|人力资源部 |诸葛亮 |25000.00|财务部 |孙尚香 |12000.00|研发部 |赵云 |15000.00|销售部 |法正 |10000.00|保卫部 | | |
该语句在 left join 之后加上了一个 lateral 关键字,使得子查询 t 能够引用前面的 department 表中的字段。
关于 MySQL 横向派生表的详细介绍和使用案例,可以参考这篇文章。
如果你使用的是 MySQL 5.7 以及之前的版本,可以利用 MySQL 中的自定义变量实现相同的效果:
select d.dept_name, w.emp_name, w.salaryfrom department dleft join ( select * from ( select a.*, if(@did = a.dept_id, @rn := @rn+1, @rn := 1) as rn, @did := a.dept_id as did from (select * from employee e order by dept_id, salary desc) a cross join (select @rn := 0 rn, @did := 0) b ) as t where t.rn <= 1 ) as w on d.dept_id = w.dept_id;
对了,在这里说一下,我目前是在职Java开发,如果你现在正在学习Java,了解Java,渴望成为一名合格的Java开发工程师,在入门学习Java的过程当中缺乏基础入门的视频教程,可以关注并私信我:01。获取。我这里有最新的Java基础全套视频教程。