SQL 基础--> 子查询

--=========================
--SQL 基础--> 子查询
--=========================
 
一、子查询
子查询就是位于SELECT、UPDATE、或DELETE语句中内部的查询
    
二、子查询的分类
单行子查询
返回零行或一行
多行子查询
返回一行或多行
多列子查询
返回多列
相关子查询
引用外部SQL语句中的一列或多列
嵌套子查询
位于其它子查询中的查询
 
三、子查询语法

SQL>
SELECT select_list
 FROM table
WHERE expr operator (SELECT select_list FROM table);

子查询(内部查询)在执行主查询之前执行一次，然后主查询(外部查询)会使用该子查询的结果

四、子查询的规则
将子查询括在括号中
将子查询放置在比较条件的右侧
只有在执行排序Top-N分析时，子查询中才需要使用ORDER BY 子句
单行运算符用于单行子查询，多行运算符用于多行子查询

五、单行子查询
仅返回一行
使用单行的表较运算符：= ,>, >= ,< , <= ,<>

--在WHERE 子句中使用子查询
SQL>
select ename, job
 from emp
where empno = (select empno from emp where mgr = 7902);

--使用分组函数的子查询
SQL>
select ename, job, sal from emp where sal > (select avg(sal) from emp);

--在HAVING子句中使用子查询
SQL>
select deptno, min(sal)
 from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);

--在FROM 子句中使用子查询
SQL>
    select empno, ename from (select empno, ename from emp where deptno = 20);
   
--单行子查询中的常见错误
--子查询的结果返回多于一行
SQL>
select empno, ename
 from emp
where sal = (select sal from emp where deptno = 20);
 
(select sal 返回多行
ORA-01427: single-row subquery returns more than one row

--子查询中不能包含ORDER BY子句
SQL>
        select empno, ename
  from emp
 where sal > (select avg(sal) from emp order by empno);
order by empno)
 
ORA-00907: missing right parenthesis
       
--子查询内部没有返回行，如下语句可以正确执行，但没有数据返回
SQL>
select ename, job
 from emp
where empno = (select empno from emp where mgr = 8000);
 
返回：no rows selected

六、多行子查询
返回多个行
使用多行比较运算符IN ,ANY ,ALL

--在多行子查询中使用IN 操作符
SQL>
select empno, ename, job
 from emp
where sal in (select max(sal) from emp group by deptno);

--在多行子查询中使用ANY 操作符
备注：
{
比如集合(1,2,3,4)
>any表示只要大于其中任意一个就行
>all表示要比其中的任何一个都大
}
SQL>
select empno, ename, job
 from emp
where sal < any (select avg(sal) from emp group by deptno);

--在多行子查询中使用ALL 操作符
SQL>
select empno, ename, job
 from emp
where sal > all (select avg(sal) from emp group by deptno);

七、相关子查询
子查询中使用了主查询中的某些字段，主查询每扫描一行都要执行一次子查询

--查询工资高于同一部门的员工的平均工资的 部门号，姓名，工资
SQL>
select deptno, ename, sal
 from emp
outer where sal > (select avg(sal)
                     from emp
                    inner where inner.deptno = outer.deptno);

--查询负责管理其它员工的员工记录(使用exists)
注：EXISTS 是判断是否存在,和in类似,但效率要比in高
SQL>
select empno, ename
 from emp
outer where exists
(select empno from emp inner where inner.mgr = outer.empno);

--查询不管理其它员工的职员(not exists)
SQL>
select empno, ename
 from emp
outer where not exists
(select empno from emp inner where inner.mgr = outer.empno)

备注：EXISTS 和NOT EXISTS 与IN 和NOT IN 的比较
EXISTS与IN的不同：
EXISTS只检查行的存在性,IN 要检查实际值的存在性(一般情况下EXISTS的性能高于IN)
NOT EXISTS 和NOT IN：
当值列表中包含空值的情况下,NOT EXISTS 则返回true,而NOT IN 则返回false.

--看下面的查询，查询部门号不在emp表中出现的部门名称及位置
SQL>
select deptno, dname, loc
 from dept d
where not exists (select 1 from emp e where e.deptno = d.deptno);

--IN与空值
SQL>
SELECT *
 FROM emp e
WHERE e.empno NOT IN (SELECT 7369
                        FROM dual
                      UNION ALL
                      SELECT NULL FROM dual);
SQL>
SELECT * FROM emp e WHERE e.empno IN (‘7369‘, NULL);

注：子查询要包含在括号内
子查询一般放在比较条件的右侧
除非进行TOP－N 分析，否则不要在子查询中使用ORDER BY。

八、多列子查询
1、成对比较

--查询工资为部门最高的记录
SQL>
select *
 from scott.emp
where (sal, job) in (select max(sal), job from scott.emp group by job);

--非成对比较,实现了与上述类似的功能
SQL>
select *
 from scott.emp
where sal in (select max(sal) from scott.emp group by job)
  and job in (select distinct job from scott.emp);

九、嵌套子查询
即位于子查询内部的子查询，嵌套层数最多可达层。然而应尽量避免使用嵌套子查询，使用表连接的查询性能会更高

SQL>
select deptno, Num_emp
 from (select deptno, count(empno) as Num_emp from emp group by deptno) d
where Num_emp > 3;

注意：子查询对空值的处理除了count(*)外，都会忽略掉空值