1、单行子查询(返回结果只有一行)
select deptno from EMP where name='smith';
select * from emp where deptno =(select deptno from EMP where name='smith');
2、多行子查询
IN
3、多列子查询
select * from EMP where (deptno,job)=(select deptno,job from emp where name='smith');
4、在from子句中使用子查询。
显示高于自己部门平均工资的员工信息:
select .....from EMP,(select AVG(sal) myavg,deptno from EMP group by deptno) ti where EMP.deptno=ti.deptno and emp.sal>ti.myavg
将查询结果当做临时表。