SQL子查询

                                   SQL子查询----实践笔记（注：备忘，自己忘记时查一查）

        子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，
检索条件又是来自该表自身的内部数据时，子查询非常有用。
    子查询可以嵌套到：where字句，having字句，from字句
    (Oracle10g SCOTT用户下的表EMP，DEPT，BONUS，SALGRADE)
    
    说明：(1).子查询要用括号括起来；
                (2).将子查询放在比较运算符的右边；
                (3).不要再子查询中使用order by子句，select语句中只能有一个order by子句，
                并且他只能是主select语句的最后一个子句；
    
    1.单行子查询
    内部select语句只返回一行结果的查询(单列)。主查询的where子句使用单行子查询的返
回结果要采用单行比较运算符(=,>,>=,<,<=,<>)。

        (1).where子句中使用单行子查询
        
        例子1：查询和雇员SCOTT同部门的雇员的姓名，工资和部门编号。
        
        select ename, job, sal, deptno
              from emp
         where deptno = (select deptno from emp where ename = 'SCOTT')
         
         例子2：查询和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员的姓名，工资和工作。
         
         select ename, job, sal
          from emp
         where job = (select job from emp where ename = 'SCOTT')
           and sal > (select sal from emp where ename = 'JAMES')
         
         (2).单行子查询中使用组函数
         
         例子1：显示工资最低的雇员姓名，工作和工资。
         
         select ename, job, sal from emp where sal = (select min(sal) from emp)
         
         例子2：显示工资高于平均工资的高于姓名，工作，工资和工资等级。
         
        select ename  AS "姓名",
               job    as "工作",
               sal    as "工资",
               grade  as "等级",
               deptno
          from emp, salgrade
         WHERE SAL > (SELECT avg(SAL) FROM EMP)
           and SAL BETWEEN LOSAL AND HISAL  
        
         注意：若将where后两个条件交换位置，查询效率会大大下降。
        
        (3).having子句中使用单行子查询
        having子句可以让我们筛选成组后的各组数据．where子句在聚合前先筛选记录．也就
是说作用在GROUP BY 子句和HAVING子句前．而 HAVING子句在聚合后对组记录进行筛选。

        例子1：显示部门内最低工资比20部门最低工资要高的部门编号及部门内最低工资。
        
        select deptno, min(sal)
          from emp
         group by deptno
        having min(sal) > (select min(sal) from emp where deptno = 20)
        
        例子2：查平均工资最低的工种名称及其平均工资 。
        
        select job, avg(sal)
          from emp
         group by job
        having avg(sal) = (select min(avg(sal)) from emp group by job)
        
        2.多行子查询
        内部select子句返回多行结果，主查询的where子句使用多行子查询返回的结果要采用
多行比较运算符，多行比较运算符(in,any,all)可以和一个或多个值进行比较。
        (1).使用in运算符的多行子查询(in运算符将等于列表中的任意一项)
        
        例子1：查询有下属的雇员的姓名，工作，工资和部门编号。(mgr为经理编号)
        
        select ename, job, sal, deptno
          from emp
         where empno in (select mgr from emp)
        
        注意：若果要查询没有下属的雇员呢？以下SQL语句是达不到效果的，将返回空；
                                                select ename, job, sal, deptno
                                                  from emp
                                                 where empno not in (select mgr from emp)
      not in运算符将会用主查询条件(empno)与子查询中的每个结果(mgr)进行逻辑非的比较。
因为子查询返回的结果中有空值存在，任何条件和空值比较都是空值。因此只要空值成为子查
询的一部分，就不能用not in运算符。修改如下：
                        select ename, job, sal, deptno
                          from emp
                         where empno not in (select mgr from emp where mgr is not null)
                或者将空值替换掉：
                        select ename, job, sal, deptno
                          from emp
                         where empno not in (select nvl(mgr,-1) from emp )                
                                                
 
        
        
        例子2：查询各部门中工资最低的雇员的姓名，工作，工资和部门编号。
        
        select ename, job, sal, deptno
          from emp
         where sal in (select min(sal) from emp group by deptno)        
        
        例子3：查询与销售部门(SALES)工作相同的其他部门的雇员的姓名,工作,工资和部门编号。
        
        select ename, job, sal, e.deptno
          from emp e, dept d
         where d.deptno = e.deptno
           and d.dname <> 'SALES'
           and job in (select distinct job
                         from emp e, dept d
                        where d.deptno = e.deptno
                          and d.dname = 'SALES')        
                          
        (2).使用any运算符的多行子查询(和内部查询返回的结果逐个比较,与单行操作符配合使用)
                                <any: 表示比子查询返回结果中的最大值小
                                =any: 表示可以是子查询返回结果中的任意一个值
                                >any: 表示比子查询返回结果中的最小值大
        例子1：查询工资低于某个文员(CLERK)雇员工作，但不从事文员工作的雇员的编号，姓名，
                     工作和工资。
        
        select empno, ename, job, sal, deptno
          from emp
         where job <> 'CLERK'
           and sal < any (select sal from emp where job = 'CLERK')        
           
        例子2：查询工作高于部门名称是SALES的部门内某个雇员的工资，但不在该部门工作的雇
员姓名，工作，工资和部门编号。
        
        select empno, ename, job, sal, e.deptno
          from emp e, dept d
         where E.DEPTNO = d.deptno
           and D.DNAME <> 'SALES'
           and sal > any (select distinct sal
                  from emp e, dept d
                 where e.deptno = d.deptno
                   and d.dname = 'SALES')    

        (3).使用all运算符的多行子查询(ALL运算符将和内部查询返回的每个结果比较)
                   >all: 比最大的大    
                             <all: 比最小的小
                            
        例子1：查询高于所有部门平均工资的雇员姓名，工作，工资和部门编号。
        
        select ename, job, sal, e.deptno
          from emp e
         where sal > all (select avg(sal) from emp group by deptno)        
        
        例子2：查询工资等级为4的雇员姓名，工作，工资，部门编号和工资等级，同时满足该
雇员工资高于部门编号为30的部门内所有员工的工资。

        select e.empno, e.ename, e.job, e.sal, e.deptno, sa.grade
          from emp e, salgrade sa
         where grade = 4
           and e.sal between losal and hisal
           and sal > all (select sal from emp where deptno = 30)


        3.多列子查询
        多列子查询返回多列结果的内部select语句，多列子查询中的列的比较有成对比较和不
成对比较，两种方法。多列子查询分为成对比较多列子查询和非成对比较多列子查询。

        先制造实践环境，对emp表的数据进行修改：
        SQL>update emp set sal=1600,comm=300 where ename='SMITH';(800,null)
        SQL>update emp set sal=1500,comm=300 where ename='CLARK';(2450,null)
        
        (1).成对比较多列子查询
        
        例子1：查询与部门编号为30的部门中任意一个雇员的工资和奖金完全相同的雇员姓名，
工资，奖金，部门编号，满足该雇员不是来自30号部门。
        
        select ename, sal, comm, deptno
          from emp
         where deptno <> 30
           and (sal, comm) in
               (select sal, nvl(comm, -1) from emp where deptno = 30)