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');

ENAME

KING

FORD

SCOTT

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);

ENAME	JOB
MILLER	CLERK

2.3 子查询中使用组函数

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

SELECT    ename, job, sal
FROM    emp
WHERE    sal = (SELECT  MIN(sal) FROM emp);

ENAME	JOB	SAL
SMITH	CLERK	800

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);

ENAME	SAL
SCOTT	3000.00
KING	5000.00
JONES	2975.00
FORD	3000.00
CLARK	2450.00
BLAKE	2850.00

3.3 ANY操作符的使用

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

• • < ANY：表示小于子查询结果集中的任意一个，即小于最大值就可以。
  • > ANY：表示大于子查询结果集中的任意一个，即大于最小值就可以。
  • = ANY：表示等于子查询结果中的任意一个，即等于谁都可以，相当于IN。

• 案例1 查询是经理的员工姓名，工资。

SELECT    ename, sal
FROM    emp
WHERE   empno = ANY (SELECT mgr
                       FROM   emp);

ENAME	SAL
JONES	2975.00
BLAKE	2850.00
CLARK	2450.00
SCOTT	3000.00
KING	5000.00
FORD	3000.00

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

SELECT empno, ename, job, sal
FROM   emp
WHERE  sal > ANY (SELECT sal
                  FROM   emp
                  WHERE  deptno = 10)
AND    deptno <> 10;

EMPNO	ENAME	JOB	SAL
7499	ALLEN	SALESMAN	1600.00
7566	JONES	MANAGER	2975.00
7698	BLAKE	MANAGER	2850.00
7788	SCOTT	ANALYST	3000.00
7844	TURNER	SALESMAN	1500.00
7902	FORD	ANALYST	3000.00

3.4 ALL操作符的使用

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

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

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

SELECT empno, ename,job, sal
FROM     emp
WHERE     sal > ALL (SELECT sal
                     FROM   emp
                     WHERE  deptno= 20)
AND    deptno <> 20;

EMPNO	ENAME	JOB	SAL
7839	KING	PRESIDENT	5000.00

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

SELECT empno, ename,job, sal
FROM     emp
WHERE     sal < ALL (SELECT sal
                     FROM   emp
                     WHERE  deptno= 10)
AND    deptno <> 10;

EMPNO	ENAME	JOB	SAL
7369	SMITH	CLERK	800.00
7521	WARD	SALESMAN	1250.00
7654	MARTIN	SALESMAN	1250.00
7876	ADAMS	CLERK	1100.00
7900	JAMES	CLERK	950.00

4. 多列子查询

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

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

SELECT deptno,ename,hiredate 
FROM emp 
WHERE (deptno,hiredate) IN (SELECT deptno ,MIN(hiredate) 
                            FROM emp 
                            GROUP BY deptno);

查询结果为

DEPTNO	ENAME	HIREDATE
20	SMITH	1980-12-17
30	ALLEN	1981-02-20
10	CLARK	1981-06-09

5. 子查询中的空值

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

SELECT    ename
FROM     emp
WHERE     empno NOT IN
		(SELECT mgr
     FROM   emp);

ENAME

• 子查询返回的结果中含有空值
• 上面的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;

ENAME	SAL	DEPTNO	SALAVG
KING	5000.00	10	2916.666667
JONES	2975.00	20	2175.000000
SCOTT	3000.00	20	2175.000000
FORD	3000.00	20	2175.000000
ALLEN	1600.00	30	1566.666667
BLAKE	2850.00	30	1566.666667