一、子查询结果返回一个值（单列单行）

选择比较运算符：> < >= <= = <>

select ename from emp_xxx
where salary > ( select salary from emp_xxx where ename = '张无忌' ) ;

二、子查询结果返回多行（单列多行）

选择比较运算符：>ALL >ANY <ALL <ANY in

select ename from emp_xxx
where salary > ALL( select salary from emp_xxx
where ename = '张无忌' ) ;

select ename from emp_xxx
where salary > ANY( select salary from emp_xxx
where ename = '张无忌' ) ;

select ename,salary,job from emp_xxx
where deptno in(select deptno from emp_xxx
where ename = '刘苍松')
and ename <> '刘苍松' ;

三、子查询结果返回多列多行

查询每个部门薪水最高的员工：

select ename, salary, job, deptno from emp_xxx
where (deptno, salary) in ( select deptno, max(salary)
from emp_xxx
where deptno is not null group by deptno ) ;

注意：
子查询的条件是单列还是多列没关系 , 关键是要分清返回的是单行还是多行。

• 如果是单行 , 用单行比较运算符 ,=,>,< 这些
• 如果是多行 , 用 in, >all, >any, <all, <any 这些

四、关联子查询

子查询不再是独立的 Sql 语句 , 需要依赖主查询传来的参数 , 这种方式叫关联子查询。

子查询又称内部查询，而包含子查询的语句称之外部查询（又称主查询）。

查询员工表中薪水比所在部门平均薪水小的员工：

select ename, salary, deptno from emp_xxx a
where salary < ( select avg(nvl(salary,0))
from emp_xxx
where deptno = a.deptno ) ;
-- 子查询不再是独立的 Sql 语句 , 需要依赖主查询传来的参数 a.deptno

查询员工表中薪水比所在部门平均薪水大的员工：

select ename,salary,deptno,
(select avg(ifnull(salary,0)) from emp_htlwk where deptno = e.deptno) avgsal 
from emp_htlwk e where salary > (select avg(ifnull(salary,0)) 
from emp_htlwk where deptno =
 e.deptno);

上述例子中，一个子查询嵌到主查询的 select 子句，一个子查询嵌到 where 子句中。

（一）子查询执行顺序

• 非关联子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。所以非关联子查询，先执行子查询，再执行主查询

• 关联子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。所以关联子查询，先执行主查询，再执行子查询

（二）exists 关键字

哪些人是其他人的经理（即查询有下属的员工）：

select ename from emp_xxx a
where exists (select 1 from emp_xxx
where mgr = a.empno) ;

• exists 关键字判断子查询有没有数据返回，有则为 ture，没有则为 false，exists 不关心子查询的结果，所以子查询中 select 后面写什么都可以，本例中我们写常量“1”

• sql 执行顺序从主查询开始 , 把主查询中的 empno 数据传入子查询 , 作为条件中的参数

哪些人不是别人的经理：

select ename from emp_xxx a
where not exists (select 1 from emp_xxx
where mgr = a.empno) ;

哪些部门没有员工：

select deptno, dname from dept_xxx d
where not exists (select 1
from emp_xxx
where deptno = d.deptno) ;

五、子查询总结

1. 非关联子查询，先执行子查询，再执行主查询，子查询只执行一次，子查询的结果作为参数传给主查询使用；
2. 关联子查询，先执行主查询，再执行子查询，主查询执行一次后，子查询跟着执行一次，主查询执行多少次，那么子查询也会执行多少次，主查询的结果作为参数传给子查询使用；
3. 子查询只能是 select 查询语句，不可以是 update、insert 等 DML 语句。

六、关联子查询示例

1.查询每个科目成绩最高的学生信息：

SELECT 
	t.stuid,  
    t.stuname,  
    t.score,  
    t.classid  
FROM 
	stugrade t  
WHERE 
	t.score = (
		SELECT 
			max(tmp.score) 
		FROM 
			stugrade tmp 
		WHERE 
			tmp.classid = t.classid
	)

2.筛选出每个部门工资排名前二的员工信息，并且按部门升序，按部门分组，部门内的员工按工资降序：

select 
	deptid,
	salary
from 
	employee a
where 
	2 > (
		select 
			count(1)
		from 
			employee b
		where 
			a.salary < b.salary 
			and 
			a.deptid = b.deptid
    )
order by 
	a.deptid,
	a.salary desc;