什么时候需要用子查询?
当查询的对象不明确时
比如查询比员工clark工资高的其他员工信息,clark的工资是不明确的,也是需要查询的
查询里面是可以嵌套查询的
但这个嵌套的只能有一个数据
select *from emp where sal>(select sal from emp where ename='CLARK')
工资大于平均工资的员工
select *from emp where sal>(select avg(sal) from emp)
select *from emp where sal>(select avg(sal) from emp)
select *from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT')
多行子查询
工资最高的员工
select *from emp where sal=(select max(sal) from emp)
关键字any ,关键字all,和多行函数的作用基本等价
select *from emp where sal>any(select sal from emp where job='CLERK')
select *from emp where sal>(select max(sal) from emp where job='CLERK')
select *from emp where sal>all(select sal from emp where job='CLERK')
in关键字表示任意存在 等价any
部门20里面和部门10里面工作一样的员工
select *from emp where job in(select job from emp where deptno=10 ) and deptno=20