SQL 子查询分析

u  单行子查询

只返回一行数据的子查询

l  查询’SMITH’同一部门的员工。

select * fromemp where deptno=(select deptno from emp whereename=’SMITH’); 该子查询返回的是SMITH的部门号，是一行数据。

u  多行子查询

返回多行数据的子查询

l  查询和部门号10的工作相同的雇员的名字，岗位，工资，部门号

select ename,job,sal,deptno from emp where job in( select distinct  job from emp where deptno=10); 该子查询返回的是10号部门的职位，是多行数据。

Ø  在子查询中使用all

显示工资比30号部门的所有员工的工资高的员工的姓名，工资和部门号。

1.    方法一 ：   select  ename,sal,deptno from empwhere sal>all(select sal from emp where deptno=30);

2.   方法二  :    select ename,sal,deptno from emp where sal>(select max(sal) from emp wheredeptno=30);

Ø  在子查询中使用any

显示工资比30号部门的任意一个员工的工资高的员工的姓名，工资和部门号

select  ename,sal,deptno from emp wheresal>any(select sal from emp where deptno=30);

select  ename,sal,deptno from emp wheresal>(select min(sal) from emp where deptno=30);

u  多列子查询

单行子查询是指子查询返回单列，单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的。而多列子查询则是指查询返回多个列数据的子查询语句。

1.   查询与SMITH的部门号和岗位完全相同的所有雇员

select * fromemp where (deptno,job)=(select deptno,jobfrom emp where ename=’SMITH’); 条件中表达式的顺序必须一致，个数必须一样。   由此可见，该子查询返回的是两列数据，因此也叫多列子查询

u  在from子句中使用子查询

在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当from子句中使用子查询时，必须给子查询指定别名。

Ø  显示高于自己部门平均工资的员工的信息。

select * from emp e,( select avg(sal) myavg,deptno from empgroup by deptno) t1 where e.deptno=t1.deptno and e.sal>t1.myavg;

 

select *from emp e where sal>(select avg(sal) from emp where deptno=emp.deptno);

ü  什么时候用子查询（单行，多行，多列），什么时候使用在from中使用子查询。

子查询结构较简单，容易理解，但扩展性低。

在from中使用子查询，扩展性强。可以在主句中得到子句中的数据。