第九章高级子查询

第九章

嵌套子查询：可以理解为一个虚拟的表

在通常的子查询中，子查询是以嵌套的方式写在 

    父查询的WHERE、HAVING、FROM子句中，所以被 

    称为嵌套子查询。 

 • 嵌套子查询的执行过程： 

      –1.子查询首先执行一次； 

      –2.用来自子查询的值确认或取消父查询的候选行。 

例：思考如何查询比本部门平均薪水高的员工姓 

   名，薪水。 

• 嵌套子查询的写法  SELECT empno,ename,sal 

   FROM        emp e ,(SELECT deptno,avg(sal) avgsal 

                             FROM emp 

                            GROUP BY deptno) d 

   WHERE e.deptno =d.deptno 

   AND e.sal >d.avgsal; 

相关子查询：

问题分析的思路： 

   父查询的候选行记录                           –1.取得父查询第一条候选行记 

   员工编号  工资  部门编号                        录的sal和deptno； 

     7369       800        20 

     7499       1600       30          –2.根据取得的deptno,获取该 

     7521       1250       30            部门的平均工资； 

     7566       2975       20 

     7654       1250       30          –3.用第一步取得的sal和第二 

     7698       2850       30            步取得的平均工资作比较，如 

     7782       2450       10 

     7788       3000       20            果sal>平均工资，则第一条候 

     7839       5000       10            选行记录被显示；否则，不被 

     7844       1500       30 

     7876       1100       20            显示； 

     7900       950        30 

                                       –4.依次取得父查询中的第2行 

     7902       3000       20 

     7934       1300       10            到最后一行，重复执行1-3。 

使用相关子查询实现： 

SELECT empno,ename, sal,deptno 

    FROM      emp outer 

    WHERE     sal >  (SELECT AVG(sal) 

                           FROM      emp 

                           WHERE     deptno = 

                                     outer.deptno) ; 

 

 父查询中的行每被处理一次，子查询就执行一次 

• 相关子查询的执行过程： 

     –1.取得父查询的候选行； 

     –2.用候选行被子查询引用列的值执行子查询； 

     –3.用来自子查询的值确认或取消候选行； 

     –4.重复步骤1、2、3，直到父查询中无剩余的候选行。 

 

                                     GET 

                            取来自父查询的候选行 

 

                                  EXECUTE 

                            用候选行值执行子查询 

 

                                    USE 

                      用子查询的值确认或取消候选行 

练习1 

 

• 如下练习，使用相关子查询完成 

• 1.查询比所在职位平均工资高的员工姓名，职位 

    SELECT ename,job
FROM emp e
WHERE sal>(SELECT AVG(sal) FROM emp WHERE job=e.job) 

• 2.查询工资为其部门最低工资的员工编号，姓名 ，工资。 

SELECT empno, ename,sal
FROM emp e
WHERE sal=(SELECT MIN(sal) FROM emp WHERE deptno=e.deptno)

 

相关子查询 ：

---查询所有部门名称和人数
---------------分组函数
SELECT d.dname,COUNT (empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname
----------嵌套子查询
SELECT d.dname, ce 人数
FROM (SELECT deptno,COUNT (empno) ce FROM emp GROUP BY deptno)c,dept d
WHERE c.deptno=d.deptno
------------相关子查询
SELECT dname,(SELECT COUNT (empno) FROM emp WHERE deptno=d.deptno)
FROM dept d

查询哪些员工是经理？

--------------
SELECT * FROM emp WHERE empno IN (SELECT DISTINCT mgr FROM emp)
相关子查询
SELECT * FROM emp e
WHERE (SELECT COUNT(empno)FROM emp WHERE mgr =e.empno)>0

 

例：查询至少调过2次岗位的员工编号，姓名， 

    岗位 

 

  SELECT e.empno, ename, e.job 

  FROM      emp e 

  WHERE  2 <= (SELECT COUNT(*) 

                     FROM     emp_jobhistory 

                    WHERE     empno = e.empno); 

练习2 

 

• 如下练习，用相关子查询完成 

• 1.查询所有雇员编号，名字和部门名字。 

SELECT empno ,ename,(SELECT dname FROM dept WHERE deptno =e.deptno)
FROM emp e

• 2.查询哪些员工是经理？ 

SELECT * FROM emp e
WHERE(SELECT COUNT (empno) FROM emp WHERE mgr=e.empno)>0

• 3.查询哪些员工不是经理？

SELECT * FROM emp e
WHERE(SELECT COUNT (empno) FROM emp WHERE mgr=e.empno)>0 

• 4.查询每个部门工资最低的两个员工编号，姓名，工资。

 SELECT *
FROM emp e
WHERE (SELECT COUNT(empno)FROM emp WHERE
deptno=e.deptno AND sal<e.sal)<=1

EXISTS和NOT EXISTS操作符 

 

• 例：查询哪些人是经理？ 

 

   SELECTename , job, sal, deptno 

   FROM              emp e 

   WHERE EXISTS (SELECT      '1' 

                     FROM     emp 

                     WHERE  mgr= e.empno); 

     –因为EXISTS子句中，并没有确切记录返回，只返回真或假。所以’1’只是占位用，无实际意义。 

例：查询哪些人不是经理？ 

  SELECT  ename, job, sal, deptno 

  FROM     emp e 

  WHERE  NOT EXISTS (SELECT  '1' 

                         FROM     emp 

                             WHERE mgr=  e.empno); 

 

     –NOT EXISTS操作符因为运算方法与NOT IN不同，只会返回TRUE或FALSE，不会返回空值，所以不需要考虑子查询去除空值的问题。 

练习3 

 

• 如下练习，用exists或not exists完成 

• 1.列出至少有一个雇员的所有部门名称。 

SELECT dname FROM dept d
WHERE EXISTS (SELECT '1' FROM emp WHERE deptno=d.deptno)

• 2.列出一个雇员都没有的所有部门名称。 

SELECT dname FROM dept d
WHERE  NOT EXISTS (SELECT'1' FROM emp WHERE deptno=d.deptno)//此处不能用count(empno)有返回值的来查询

课后作业 ：

• 如下练习，使用相关子查询完成。 

• 1.查询薪水多于他所在部门平均薪水的雇员名字 ，部门号。 

SELECT ename,deptno
FROM emp e
WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=e.deptno)

• 2.查询员工姓名和直接上级的名字。

SELECT ename,(SELECT ename FROM emp WHERE empno=e.mgr)
FROM emp e 

• 3.查询每个部门工资最高的员工姓名，工资。

SELECT *
FROM emp e
WHERE sal IN (SELECT MAX(sal)FROM emp GROUP BY deptno)--------------嵌套子查询

或者
SELECT *
FROM emp e
WHERE (SELECT COUNT(empno) FROM emp WHERE deptno=e.deptno AND sal>e.sal)<=0 相关子查询

• 4.查询每个部门工资前两名高的员工姓名，工资。 

SELECT *
FROM emp e
WHERE (SELECT COUNT(empno)FROM emp WHERE
deptno=e.deptno AND sal>e.sal)<=1

 

----exists，not exists 跟相关子查询一起使用

-----in，not in  跟嵌套子查询一起用