关于My SQL中EXISTS在相关子查询的应用

1.子查询

子查询就是嵌套在查询语句内的查询,后续查询可以使用先前查询的结果,用以代替暂存表或变量存储先前查询结果。 子查询的外层为较高的层级,以下称之为“主查询”。派生表是子查询的一个特殊情况,他是应用于FROM子句中的 SELECT完整命令,以数据表别名引用
子查询就是嵌套在查询语句内的查询,后续查询可以使用先前查询的结果,用以代替暂存表或变量存储先前查询结果。
子查询的外层为较高的层级,以下称之为“主查询”。派生表是子查询的一个特殊情况,他是应用于FROM子句中的
SELECT完整命令,以数据表别名引用此子查询时,同使用一般数据表一样。
子查询以内外层是否进行连接(JOIN)可分成嵌套子查询相关子查询两种类型。

2.嵌套子查询

嵌套子查询一般可以分为:返回单值的子查询 和 返回一个列表的子查询 。
例如:查询选修课程号为’101’并且成绩高于学生号为’9501101’的所有学生的成绩.

select * from sclass 
where cno='101' and degree>=
(select degree from sclass where sno='9501101'and cno='101') 

当子查询跟随在 =、!=、<、<=、>、>= 之后, 子查询的返回值只能是一个, 否则应在外层where子句中用一个in限定符,即要返回多个值,要用in或者not in。
例如:查询现有部门的所有员工

select ename,job,deptno
from emp
where deptno in(SELECT
deptno 
from dept)

3.相关子查询

相关子查询是指引用了外部查询列的子查询,即子查询会对外部查询的每行进行一次计算。
查询emp表中每个部门中最高工资的员工编号,姓名,职位和工资。

select empno,deptno,ename,job,sal
from emp e
where sal =(select max(sal)
from emp em
where e.deptno=em.deptno)

说明:由外查询提供一个部门名称给内查询,内查询利用这个部门名称找到该部门的最高基本工资,然后外查询根据基本工资判断是否等于最高工资,如果是的,则显示出来.
相当于: 先查找每个部门的最高工资作为表t,然后通过相同列部门编号连接表t和表emp,找到工资为最高工资的员工。

select empno,e.deptno,ename,job,sal
from emp e ,
(select deptno,max(sal) as max_sal
from emp 
group by deptno )as t
where e.deptno=t.deptno
and sal=max_sal

4.相关子查询与嵌套子查询的执行过程有什么不同?

嵌套子查询的执行不依赖与外部的查询。
执行过程:
(1)执行子查询,其结果不被显示,而是传递给外部查询,作为外部查询的条件使用。
(2)执行外部查询,并显示整个结果。

相关子查询的执行依赖于外部查询。
多数情况下是子查询的WHERE子句中引用了外部查询的表。
执行过程:
(1)从外层查询中取出一个元组,将元组相关列的值传给内层查询。
(2)执行内层查询,得到子查询操作的值。
(3)外查询根据子查询返回的结果或结果集得到满足条件的行。
(4)然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。
嵌套子查询与相关子查询的差别在于:子查询是否可以单独单独执行,相关子查询必须与外层查询相互关联,外层查询必须将每一笔数据传入子查询进行比对,符合子查询的数据最后才会被外层查询传回。
非相关子查询是独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询。 相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次。 故非相关子查询比相关子查询效率高。

5.exists谓词

MySQL中EXISTS语法为:

  • SELECT … FROM table WHERE EXISTS (subquery)

该语法可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。
实例:
查找出在 DEPT 表中存在而在 EMP 表里却不存在的部门编号(如果有的话)。
not in 写法:

 select deptno
 from dept
 where deptno not in (select deptno from emp)

exists/not exists写法:

select deptno
from dept
where not exists(select null    
from emp
where emp.deptno=dept.deptno)

#Select null 与Select 1 类似,满足条件的行返回列值Null,在与Exists配合使用时,只要有行返回,则Exists子查询仍然为True。
述查询语句遍历并评估 DEPT 表的每一行。针对每一行,会有如下操作。
(1) 执行子查询并检查当前的部门编号是否存在于 EMP 表。要注意关联条件 D.DEPTNO =E.DEPTNO,它通过部门编号把两个表连接起来。
(2) 如果子查询有结果返回给外层查询,那么 EXISTS (…) 的评估结果是 TRUE,这样 NOTEXISTS (…) 就是 FALSE,如此一来,外层查询就会舍弃当前行。
(3) 如果子查询没有返回任何结果,那么 NOT EXISTS (…) 的评估结果是 TRUE,由此外层查询就会返回当前行(因为它是一个不存在于 EMP 表中的部门编号)。

6.使用exists和关连子查询的好处

为了避免 NOT IN 和 Null 值带来的问题

SQL 中,TRUE or NULL 的运算结果是 TRUE,但 FALSE or NULL 的运算结果却是 Null !一旦混入了 Null,结果就会一直保持为 Null(当使用 IN 谓词以及当执行 OR 逻辑运算的时候,你要想到是否会涉及 Null 值。为了避免 NOT IN 和 Null 值带来的问题,需要结合使用 NOT EXISTS 和关联子查询关联。