多行子查询

• 多行子查询返回的记录有多条
• 可以使用的操作符 in / all // any

in

查询是经理的员工姓名, 工资

-- SELECT ename, sal from emp
-- where EMPNO in (SELECT mgr from emp)
SELECT ename, sal from emp
where EMPNO in (7902, 7698, 7839, 7566, 7788, 7782)
-- SELECT DISTINCT mgr from emp

any 表示和子查询的任意一行进行比较有一个满足就行

查询是经理的员工姓名, 工资

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

• ANY 表示大于子查询结果集合中的任意一个, 即大于最小值就可以

• = ANY表示等于子查询结果集合中的任意一个, 即等于谁都可以

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

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部门员工
• 查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工
• 查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工

select ename, HIREDATE from emp
where hiredate> any (select hiredate from emp where deptno=10) and deptno <> 10

select ename, HIREDATE from emp
where hiredate> all (select hiredate from emp where deptno=10) and deptno <> 10

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

子查询中有空的情况

SELECT ename, sal from emp
where EMPNO not in (SELECT mgr from emp)

上面子查询中计算包含了空值, 因为有一条值控制, 会导致主查询返回没有记录, 结论: 只要空值有可能成为子查询的一部分, 就不能使用 not in

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

• 查询部门平均工资在2500元以上的部门名称及平均工资。
• 查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。
• 查询部门人数在2人以上的部门名称、最低工资、最高工资。
• 查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。
• 显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序
• 查询工资高于编号为7782的员工工资，并且和7369号员工从事相同工作的员工的编号、姓名及工资。
• 查询工资最高的员工姓名和工资。
• 查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
• 查询员工工资为其部门最低工资的员工的编号和姓名及工资。
  显示经理是KING的员工姓名，工资。
• 显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间