单列单行子查询
- select * from emp where sal>(select max(sal) from emp where deptno=30);//(效率高)
查询工资比30号部门的所有员工的工资都高的员工信息
- select * from emp where sal>(select min(sal) from emp where deptno=30);//(效率高)
查询工资比30号部门的任意员工的工资都高的员工信息
单列多行子查询
- select * from emp where sal>all(select sal from emp where deptno=30);//(效率低)
查询工资比30号部门的所有员工的工资都高的员工信息
- select * from emp where sal>any(select sal from emp where deptno=30)//(效率低)
查询工资比30号部门的任意员工的工资都高的员工信息
还有in就不写例子了
多列单行子查询
select * from emp where (deptno,job)=(select deptno,job from emp where ename='xyz')
查询和xyz相同deptno和job的员工信息
多列多行子查询(内嵌视图)
select a2.ename,a2.sam,a2.deptno,a1.mysql from a2,
(select deptno,avg(sal) as mysal from emp group by deptno ) a1
where a2.deptno=a1.deptno and a2.sal>a1.mysal
查询所有高于其本部门平均工资的员工信息(给表起别名不能加as)