嵌套子查询--基于Orace scott用户

SQL提供嵌套子查询，子查询是嵌套在另一个查询中的select-from-where 表达式。

集合成员资格

连接词 in 测试元组是否是集合中的成员，not in 相反

查询部门号为10和20的员工信息：

select *
from emp
where deptno in (10, 20);

where子句中的子查询

子查询在where子句中，通常对于集合的成员资格、集合的比较以及集合的基数进行检查。

1.集合成员资格

单行单列子查询
查询部门名字为SALES的员工信息：
//因为部门名字为SALES的部门只有一个，所以可以使用 = ，当然也可以使用 in

select ename
from emp
where deptno in (
  select deptno 
  from dept
  where dname = 'SALES');

多行单列自查询
查询每个部门中最低工资的员工信息：

select deptno, ename, sal
from emp
where sal in(
  select min(sal)
  from emp
  group by deptno);

多列子查询
查询和30号部门中的某一位员工的薪金和奖金相同的员工信息：

select *
from emp
where (sal, nvl(comm, 0)) in (
  select sal, nvl(comm, 0)
  from emp
  where deptno = 30);

nvl（comm, 0) 如果comm为空，返回0

2.集合的比较

some和any ：至少比一个大

查询工资大于任意一个部门平均工资的员工信息：

select *
from emp
where sal > some(
  select avg(sal)
  from emp
  group by deptno);

all：大于所有
查询工资大于所有部门平均工资的员工信息：

select *
from emp
where sal > all(
  select avg(sal)
  from emp
  group by deptno);

3.空关系测试

exists 结构在作为参数的子查询中返回true值
该查询语句中，外层select返回的每一行数据都要根据子查询来评估。

查询在’NEW YORK’工作的员工信息：

select *
from emp e
where exists(
  select * 
  from dept d
  where e.deptno = d.deptno and d.loc = 'NEW YORK');

//上述查询还说明了sql的一个特性，来自外层查询的一个相关名称可以用在where子句的子查询中。这种子查询被称为相关子查询。

查询不在’NEW YORK’工作的员工信息：

select *
from emp e
where not exists(
  select * 
  from dept d
  where e.deptno = d.deptno and d.loc = 'NEW YORK');

from子句中的子查询

sql允许在from中使用子查询表达式，采用的主要观点支撑是：select-from-where返回的都是关系

查询部门的工资总和最高的工资：

select max(sum_sal)
from (
  select sum(sal) as sum_sal
  from emp
  group by deptno) ;

查询公司中工资最高的前三个员工的信息:

select ename,sal
from (
  select ename,sal 
  from emp
  order by sal desc)
where rownum <= 3;