5.oracle子查询

子查询;
N行N列概念：
1.单行单列：无论在行上还是列上都只有一个
2.单行多列：查询结果仅有一行数据，但是在列上不知一列
3.多行单列：查询结果仅有一列数据，但是行数上不止一行
4.多行多列：无论在行上还是列上都不知一个

表格解析：
SELECT          FROM          WHERE           GROUP BY             HAVING         ORDER BY
单行单列      单行单列       单行单列         不允许              单行单列         单行单列(无意义)
              单行多列       单行多列                             单行多列
              多行单列       多行单列                             多行单列
              多行多列       多行多列                             多行多列

子查询定义： 在一个查询语句中嵌入其他查询语句
子查询用途：查询作为一个集合跟在from，where 和 having子句中最多，还可以用在DML语句中;
--子查询中如果有null值，就不能使用NOT IN 运算符
SELECT ENAME FROM EMP WHERE MGR=(select empno FROM EMP WHERE ENAME='BLAKE');
SELECT ENAME FROM EMP WHERE MGR=(SELECT MGR FROM EMP WHERE ENAME='BLAKE');
SELECT EMPNO FROM EMP WHERE MGR=(SELECT MGR FROM EMP WHERE ENAME='BLAKE');
select * from emp where sal=(select max(sal) from emp);

select deptno from EMP where ename in ('SMITH','ALLEN');
SELECT * FROM EMP WHERE DEPTNO IN (select deptno from EMP where ename in ('SMITH','ALLEN'));

SELECT DEPTNO,SAL FROM EMP WHERE ENAME='SMITH';
SELECT * FROM EMP WHERE (DEPTNO,SAL)=(SELECT DEPTNO,SAL FROM EMP WHERE ENAME='SMITH') AND ENAME!='SMITH';

SELECT AVG(SAL) FROM EMP;
SELECT deptno FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>(SELECT AVG(SAL) FROM EMP);

select deptno from emp group by deptno having count(ename)>0;
select * from emp where deptno in (select deptno from emp group by deptno having count(ename)>0);

--子查询作为数据源时，当子查询语句中某字段出现了函数，需要对该字段起别名才能被主查训引用
select * from (select max(sal) as sal_max from emp) where sal_max=5863;

select round(avg(sal)) sal_avg,deptno from emp group by deptno;
SELECT max(sal_avg) FROM (select round(avg(sal)) sal_avg,deptno from emp group by deptno);
select deptno from (select round(avg(sal)) sal_avg,deptno from emp group by deptno) 
where sal_avg=(SELECT max(sal_avg) FROM (select round(avg(sal)) sal_avg,deptno from emp group by deptno));

select ename,job,sal,(select dname from dept where deptno=10) as dname
,(select loc from dept where deptno=10) as loc from emp where deptno=10;

--子查询的分类select ename from emp e1 where
/*非相关子查询： 子查询语句能够单独运行，子查询中不牵扯主查训的任何内容，主查训在乎的是与子查询的结果
相关子查询：   子查询语句不能单独运行，子查询牵扯主查训中的一部分内容，主查训只在乎与子查询的关系
非相关子查询可以用在任何位置，相关子查询京用于where和having中作为条件，且绝大多数情况两者可相互转换，
非相关子查询在书写和理解上更加容易，而相关子查询之所以存在，是因为很多情况下相关子查询运行效率更高
*/


相关子查询的关键词：
      exists 对应条件查询中的 in
      not exists  对应条件查询中的 not in;
      
相关子查询：执行查询的时候先取得外层查询的一个属性值，然后执行与此属性值相关的子查询，执行完毕后再取得外
            层父查询的下一个值，依次再来重复执行子查询；
不相关子查询：子查询的执行不需要提前取得父查询的值，只是作为父查询的查询条件

--两者区别
/*查询同样的数据，不同的方法，效率不同
两者都能实现表功能查询，主要区别如下：
1、适用表的类型不同。
in是子查询为驱动表，外面的表为被驱动表，故适用于子查询结果集小而外面的表结果集大的情况。
exists是外面的表位驱动表，子查询里面的表为被驱动表，故适用于外面的表结果集小而子查询结果集大的情况。

2、子查询关联不同。
exists一般都是关联子查询。对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内层查询。
       外层查询和内层查询相互依赖，因为外层查询会把数据传递给内层查询。
in     则一般都是非关联子查询，非关联子查询则必须先完成内层查询之后，外层查询才能介入。

3、执行次数不同。
IN 语句：只执行一次，确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，
         然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。
EXISTS语句：执行次数根据表的长度而定。指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有
            没有和内表的数据一样的。匹配上就将结果放入结果集中。*/
            
===EXISTS 相当于看子查询是否有返回结果，有则为 true 保留外表的该行作为结果，没有则返回 false 去除外表该行===;
SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');
SELECT * FROM EMP E1 WHERE EXISTS (SELECT 1 FROM EMP E2 WHERE ENAME='SMITH' AND E2.DEPTNO=E1.DEPTNO);

select ename from emp e1 where exists (select 1 from emp e2 where ename='BLAKE' and e1.mgr=e2.empno);
select ename from emp  where mgr=(select empno from emp where ename='BLAKE');


在多行子查询中，我们有三个关键字，分别是：
     IN：在…之间
     ANY：任意一个
2.1：>ANY：比最小值大
2.2：<ANY：比最大值小
2.3：=ANY：跟IN一样的用法
      ALL：全部
3.1：>ALL：比最大的还大
3.2：<ALL：比最小的还小;