oracle子查询引用外表_Oracle子查询（嵌套查询）

概念：

所谓子查询，即一个select语句中嵌套了另外的一个或者多个select语句

需求：查找和Smith同部门的所有员工的id和last_name

目标： 员工id,last_name

from:  s_emp

条件： s_emp.dept_id = Smith所在部门的id？

select id,last_name

from s_emp

where dept_id = ?

阶段目标： Smith所在部门的id

目标： dept_id

from : s_emp

条件： last_name = 'Smith';

select dept_id

from s_emp

where last_name = 'Smith';

组合：

select id,last_name

from s_emp

where dept_id = (

select dept_id

from s_emp

where last_name = 'Smith'

)

应用场景：

1.一条查询语句的查询条件依赖另外一条查询语句的查询结果。

2.一条查询语句的查询结果是作为另外一条查询语句的查询表(查询依据)。

3.在DML操作中使用子查询(后期介绍)

子查询的基本原则：

1.在查询中可以有单行子查询和多行子查询

2.子查询可以出现在操作符的左边或者右边

3.子查询在很多sql命令中都可以使用

4.嵌套查询先执行，然后将结果传递给主查询。

一、比较值不确定，需要另外一个select语句执行后才能得到，使用子查询

语法：

select ...

from ...

where col_name 比较操作符(

select ...

from ...

where ...

group by ...

having...

)

group by ...

having...

order by ...

单值子查询：子查询的结果为1个

需求：

1.查询和Simith职称相同的所有员工的last_name和职称

分析步骤：

1.确定最终查询结果(目标/主查询)：查询员工的last_name和title

from : s_emp

条件 ： title = Smith的职称

select last_name,title

from s_emp

where title = ?

2.确定条件(子查询)：Smith的职称

from : s_emp

条件 ：last_name = 'Smith';

select title

from s_emp

where last_name = 'Smith';

3.组合

select last_name,title

from s_emp

where title = (select title

from s_emp

where last_name = 'Smith');

2.查看工资大于Chang员工工资的所有员工的id和名字。

最终目标：员工的id,last_name

from：s_emp

条件： salary > Chang员工的工资

select id,last_name

from s_emp

where salary > ?

阶段目标：Chang员工的工资

from ：s_emp

条件： last_name = 'Chang';

select salary

from s_emp

where last_name = 'Chang';

组合：

3.查看员工工资小于平均工资的所有员工的id和名字

例如：查找和Smith同一个部门的员工的id和last_name

多值子查询：子查询的结果为多个

需求：

1.查询所在区域为2号区域的所有部门的员工的id和last_name

1.确定最终查询结果： 员工的id, last_name

from : s_emp

条件 ：s_emp.dept_id  in (?);

select id,last_name

from s_emp

where dept_id  in ?

2.确定条件：所在区域为2号部门id

子查询：部门id

from : s_dept

条件: region_id = 2;

select id

from s_dept

where region_id = 2;

3.组合：

select id,last_name

from s_emp

where dept_id  in (

select id

from s_dept

where region_id = 2

)

子查询出现情况二：

查找的内容不确定，需要从构建出来一个查询的表

语法：

select ....

from (select .... from ....) b

where ......

练习：查询各部门的id，name和部门员工的平均工资

1.查询目标：

需要部门的id，部门的name ------从s_dept表中

部门员工的平均工资 avg(salary) --------- salary只有s_emp表中有

条件 ： 部门id，name和部门 员工，因此要求部门的id跟员工所在部门的id相等才连接

select id,name, 平均工资

from s_dept , ?

where s_dept.id = ?.dept_id;

2.查询条件

select(dept_id,avg(salary) sal)

from s_emp

group by dept_id;

3.组合：

select id,name,b.sal

from s_dept dept,(select dept_id,avg(salary) sal

from s_emp

group by dept_id

) b

where dept.id = b.dept_id;