Class_4:
嵌套子查询：（可以转换成表连接）
代码语言：javascript复制-- 把查询语句嵌套在where筛选条件中：
select ename
from emp
where hiredate > (select hiredate
from emp
where ename = 'king')
-- 里面这个子查询返回一行可以直接使用比较运算符，因为就这一个人这个名字如果子查询可能返回多行？ – 把运算符号换成in
代码语言：javascript复制select name
from student
where ID in(筛选出来的很多行)用 in 和 not in 查询是否存在于查询集合中：
代码语言：javascript复制select ENAME,EMPTNO,SAL
from EMP
where DEPTNO in (select DEPTNO
from DEPT
where LOC = 'NewYork')
select count(salary)
from EMP
where DEPTNO in (Select DEPTNO
from EMP
where ENAME = 'blake')双嵌套：
代码语言：javascript复制select *
from EMP
where DETPNO not in(Select DEPTNO
from EMP
where ENAME = 'blake')
and JOB in (select JOB
from EMP
where DEPTNO in (Select DEPTNO from EMP where ENAME = 'blake')
)SOME 和 ALL：
运算符 some：满足一个就行
代码语言：javascript复制select name
from instructor
where salary > some (select salary from instructor where dept_name='Biology')
-- 查找出来老师只要工资 比生物系最起码一个老师的工资高就行。运算符 all ：必须要全部满足才可以
=all 和 in 不是一个概念，in 是满足一个
<>some 和 not in 不是一个概念some=
<>all 和 not in 一个概念
=some 和 in 一个概念
代码语言：javascript复制 = <> >= <=
some in -- >=min <=max
all -- not in <=max <=minselect name
from student
where id <>all(select id
from takes
where course_id = 'cd=347')
select DEPTNO,AVG(SAL)
from EMP
group by DEPTNO
having AVG(SAL) >ALL AVG(select SAL from EMP group by DEPTNO)
select ENAME
from EMP
where EMPNO in (select MGR from EMP)
-- 陷阱：如果是not in，一定要小心NULL，因为NULL运算完了什么也找不到。
-- 加一个
EMPNO is not NULL
