-- 把查询语句嵌套在where筛选条件中:
select ename
from emp
where hiredate > (select hiredate
from emp
where ename = 'king')
-- 里面这个子查询返回一行可以直接使用比较运算符,因为就这一个人这个名字
如果子查询可能返回多行? – 把运算符号换成in
select name
from student
where ID in(筛选出来的很多行)
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')
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')
)
select name
from instructor
where salary > some (select salary from instructor where dept_name='Biology')
-- 查找出来老师只要工资 比生物系最起码一个老师的工资高就行。
= <> >= <=
some in -- >=min <=max
all -- not in <=max <=min
select 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