9. DQL-子查询

      本小节我们开始学习子查询，理解子查询的含义，掌握单行子查询和多行子查询的使用。 

1. 子查询概述

1.1 为什么使用子查询

      思考如下问题？

            查询工资比Jones工资高的员工信息？

            查询工资最低的员工姓名？

      “谁的薪水比 Jones还高呢？”

      子查询语法

select select_list
from table
where expr operator
	  (select select_list
       from table);

      括号内的查询叫做子查询，也叫内部查询，先于主查询执行。

      子查询的结果被主查询（外部查询）使用

      expr operator 包括比较运算符

            单行运算符：> , = , >= , < , <> , <=

            多行运算符：in，any，all

      子查询可以嵌于以下sql语句中:

            where子句

            having子句

            from子句 

1.2 子查询用法

      查询出比JONES为雇员工资高的其他雇员

select ename
from emp
where sal > 
	(select sal
 	 from emp   
 	 where ename='JONES');

 

1.3 子查询分类

      根据子查询返回的行数量，分为：

            单行子查询

            多行子查询

            多列子查询

1.4 子查询使用指导

      子查询要用括号括起来

      将子查询放在比较运算符的右边

      对于单行子查询要使用单行运算符

      对于多行子查询要使用多行运算符

2. 单行子查询

2.1 单行子查询概述

      子查询只返回一行一列

      使用单行运算符

运算符	含义
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

2.2 单行子查询语句

      显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作。

select ename, job  
from emp
where job = 
(select job from emp where empno = 7369)  
and sal > 
(select sal from emp where empno = 7876);

 

2.3 子查询中使用组函数

      查询工资最低的员工姓名，岗位及工资

select ename,job,sal
from emp
where sal = (select min(sal) from emp);

 

2.4 HAVING子句中使用子查询

      查询部门最低工资比20部门最低工资高的部门编号及最低工资

select deptno,min(sal)
from emp
group by deptno
having min(sal) >
    (select min(sal)
     from emp
     where deptno = 20);

3. 多行子查询

3.1 多行子查询概述

      子查询返回记录的条数 可以是一条或多条。

      和多行子查询进行比较时，需要使用多行操作符，多行操作符包括：

            IN

            ANY

            ALL

      IN操作符和以前介绍的功能一致，判断是否与子查询的任意一个返回值相同。

3.2 IN操作符的使用

      查询是经理的员工姓名，工资。 

select ename, sal
from emp
where empno in (select mgr
                from emp);

 

3.3 ANY操作符的使用

      ANY：表示和子查询的任意一行结果进行比较，有一个满足条件即可。

            < ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。

            > ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。

            = ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

      查询是经理的员工姓名，工资。

select ename,sal
from emp
where empno = any (select mgr
                   from emp);

      查询部门编号不为10，且工资比10部门任意一名员工工资高的员工编号，姓名，职位，工资。

select empno,ename,job,sal
from emp
where sal > any (select sal
                 from emp
                 where deptno = 10)
and deptno <> 10;

 

3.4 ALL操作符的使用

      ALL：表示和子查询的所有行结果进行比较，每一行必须都满足条件。

            < ALL:表示小于子查询结果集中的所有行，即小于最小值。

            >ALL:表示大于子查询结果集中的所有行，即大于最大值。

            = ALL :表示等于子查询结果集中的所有行,即等于所有值，通常无意义。

      查询部门编号不为20，且工资比20部门所有员工工资高的员工编号，姓名，职位，工资。

select empno,ename,job,sal
from emp
where sal > all (select sal
                 from emp
                 where deptno= 20)
and deptno <> 20;

      查询部门编号不为10，且工资比10部门所有员工工资低的员工编号，姓名，职位，工资。

select empno,ename,job,sal
from emp
where sal < all (select sal
                 from emp
                 where deptno= 10)
and deptno <> 10;

 

4. 多列子查询

      除了常用单行子查询和多行子查询外，还有特殊情况使用的多列子查询，此部分内容大家了解即可，以便于后续在笔试中遇见此类问题可以及时解决。

      例：查询每个部门入职时间最早的部门编号，员工姓名，入职时间

select deptno,ename,hiredate 
from emp 
where (deptno,hiredate) in (select deptno ,min(hiredate) 
                            from emp 
                            group by deptno);

5. 子查询中的空值

      查询不是经理的员工姓名。

select ename
from emp
where empno not in
		   (select mgr
            from emp);

      子查询返回的结果中含有空值

      上面的SQL语句试图查找出没有下属的雇员，逻辑上，这个SQL语句应该会返回8条记录，但是却一条也没返回，why?

      因为子查询的结果中有一条空值，这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。

6. 在 FROM 子句中使用子查询

      查询比自己部门平均工资高的员工姓名，工资，部门编号，部门平均工资

select a.ename,a.sal,a.deptno,b.salavg
from emp a, (select deptno,avg(sal)salavg
             from emp
             group by deptno) b
where a.deptno = b.deptno and a.sal > b.salavg;