Oracle学习——Oracle的子查询——主查询、子查询

Oracle的子查询

何为子查询：子查询时嵌入在其他SQL语句中的SELECT语句，也称嵌套查询

举例：查询和SMITH是同一个职位的员工

//这是在where子句中使用子查询
		Select *
		From emp
		Where job = (select job 
		from emp
		where ename = ‘SMITH’);
//第一步 查询SMITH的职位
//第二步 查询和SMITH是同一个职位的员工

子查询语法：

	SELECT  select_lis
	FROM  table
	WHERE  expr operator
					(SELECT  select_list
FROM  table);

可以使用子查询的位置： where, select, having, from

在select子句中使用子查询

举例：查询出每个部门的编号、名称、位置、部门人数。

Select deptno,dname,loc, (select count(empno) 
from emp
where emp.deptno = dept.deptno ) from dept;

在having子句中使用子查询

举例：查询员工信息表，按部门编号进行分组，要求显示员工的部门编号、平均工资，查询条件是平均工资大于30号部门的最高工资

Select deptno,avg(sal)
from emp 
group by deptno
having avg(sal)> (selct max(sal) from emp where deptno = 30);

在from子句中使用子查询：把子查询看成一张新的表

Select * from (select empno,ename,sal from emp);

举例：查询并显示高于部门平均工资的雇员信息

Select empno,ename,sal
 	From emp,(select deptno,avg(sal) avgsal from emp group by deptno) dept(别名)
	Where emp.deptno = dept.deptno and sal > avgsal;

主查询和子查询

简单来说括号里的就是子查询，也叫内查询

一个主查询可以有多个子查询：

举例：显示职位和7521的职位相同并工资大于7934这个员工工资的员工信息

select *
from emp
where job = (select job from emp whereempno = 7521)
and sal > (select sal from emp where empno = 7934)

子查询的执行顺序：一般先执行子查询，再执行主查询，但相关子查询例外。

举例：查询员工表中小于平均工资的员工信息

Select *
	From emp
	Where sal < (select avg(sal) from emp);

相关子查询：当查询需要引用主查询的表列时，oracle会执行相关子查询相关子查询是先执行主查询，再执行子查询。

举例：查询工资高于部门平均工资的雇员名，工资和部门号。

Select ename,sal,deptno
	From emp e	//这里需要主查询给e.depeno传信息，所以会先执行主查询
	Where sal > (select avg(sal) from emp where deptno = e.deptno);

主查询和子查询可以不是同一张表

举例：查询部门名称是ACCOUNTING的员工信息

Select * 
	From emp
	Where deptno = (select deptno from dept where dname=’ACCOUNTING’);

用多表连接查询方法

Select * 
	From emp e,dept d
	Where e.deptno = d.deptno and d.dname=’ACCOUNTING’;

注意：
尽量使用多表连接查询方法 因为只访问一次 子查询访问两次
这是理论上的，因为理论上不考虑笛卡尔集