连接查询和子查询的详解

--=========================

--SQL 基础--> 子查询

--=========================  

 

/*

一、子查询

    子查询就是位于SELECT、UPDATE、或DELETE语句中内部的查询

   

二、子查询的分类

    单行子查询

        返回零行或一行

    多行子查询

        返回一行或多行

    多列子查询

        返回多列

    相关子查询

        引用外部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

      2  where empno = (

      3    select empno from emp

      4    where mgr = 7902 );

 

    ENAME      JOB

    ---------- ---------

    SMITH      CLERK

   

    --使用分组函数的子查询

    SQL> select ename,job,sal

      2  from emp

      3  where sal >

      4    (select avg(sal) from emp);

 

    ENAME      JOB              SAL

    ---------- --------- ----------

    JONES      MANAGER         2975

    BLAKE      MANAGER         2850

    CLARK      MANAGER         2450

    SCOTT      ANALYST         3000

    KING       PRESIDENT       5000

    FORD       ANALYST         3000

   

    --在HAVING子句中使用子查询

    SQL> select deptno,min(sal)

      2  from emp

      3  group by deptno

      4  having min(sal) >

      5      (select min(sal)

      6       from emp

      7       where deptno = 20);

 

       DEPTNO   MIN(SAL)

    ---------- ----------

           30        950

           10       1300

          

    --在FROM 子句中使用子查询

    SQL> select empno,ename

      2  from

      3      (select empno,ename

      4       from emp

      5       where deptno = 20);

 

        EMPNO ENAME

    ---------- ----------

         7369 SMITH

         7566 JONES

         7788 SCOTT

         7876 ADAMS

         7902 FORD

   

    --单行子查询中的常见错误

        --子查询的结果返回多于一行

       SQL> select empno,ename

         2  from emp

         3  where sal =

         4      (select sal     

         5       from emp

         6       where deptno = 20);

           (select sal

            *

       ERROR at line 4:

       ORA-01427: single-row subquery returns more than one row

      

       --子查询中不能包含ORDER BY子句

       SQL> select empno,ename

         2  from emp

         3  where sal >

         4      (select avg(sal)

         5       from emp

         6       order by empno);

            order by empno)

            *

       ERROR at line 6:

       ORA-00907: missing right parenthesis

      

       --子查询内部没有返回行，如下语句可以正确执行，但没有数据返回

       SQL> select ename,job

         2  from emp

         3  where empno =

         4      (select empno

         5       from emp

         6       where mgr = 8000);

 

       no rows selected

 

    /* 

六、多行子查询

    返回多个行

    使用多行比较运算符IN ,ANY ,ALL

   

    在多行子查询中使用IN 操作符  */

    SQL> select empno,ename,job

      2  from emp

      3  where sal in

      4      (select max(sal)

      5       from emp

      6       group by deptno);

 

        EMPNO ENAME      JOB

    ---------- ---------- ---------

         7698 BLAKE      MANAGER

         7902 FORD       ANALYST

         7788 SCOTT      ANALYST

         7839 KING       PRESIDENT

        

    --在多行子查询中使用ANY 操作符

    SQL> select empno,ename,job

      2  from emp

      3  where sal < any

      4      (select avg(sal)

      5       from emp

      6       group by deptno);

 

        EMPNO ENAME      JOB

    ---------- ---------- ---------

          7369 SMITH      CLERK

         7900 JAMES      CLERK

         7876 ADAMS      CLERK

         7521 WARD       SALESMAN

         7654 MARTIN     SALESMAN

         7934 MILLER     CLERK

         7844 TURNER     SALESMAN

         7499 ALLEN      SALESMAN

         7782 CLARK      MANAGER

         7698 BLAKE      MANAGER

        

    --在多行子查询中使用ALL 操作符

    SQL> select empno,ename,job

      2  from emp

      3  where sal > all

      4      (select avg(sal)

      5       from emp

      6*      group by deptno)

   

        EMPNO ENAME      JOB

    ---------- ---------- ---------

         7566 JONES      MANAGER

         7788 SCOTT      ANALYST

         7839 KING       PRESIDENT

         7902 FORD       ANALYST

   

     /*

七、相关子查询

    子查询中使用了主查询中的某些字段，主查询每扫描一行都要执行一次子查询 */

 

    --查询工资高于同一部门的员工的部门号，姓名，工资

    SQL> select deptno,ename,sal

      2  from emp outer

      3  where sal >

      4      (select avg(sal)

      5       from emp inner

      6       where inner.deptno = outer.deptno);

 

       DEPTNO ENAME             SAL

    ---------- ---------- ----------

           30 ALLEN            1600

           20 JONES            2975

           30 BLAKE            2850

           20 SCOTT            3000

           10 KING             5000

           20 FORD             3000

          

    --查询负责管理其它员工的员工记录(使用exists)

    SQL> select empno,ename

      2  from emp outer

      3  where exists

      4     (select empno

      5      from emp inner

      6      where inner.mgr = outer.empno);

 

        EMPNO ENAME

    ---------- ----------

         7566 JONES

         7698 BLAKE

         7782 CLARK

         7788 SCOTT

         7839 KING

         7902 FORD

    --查询不管理其它员工的职员(not exists)

    SQL> l3

      3* where exists

    SQL> c /where/where not

      3* where not exists

    SQL> l

      1  select empno,ename

      2  from emp outer

      3  where not exists

      4     (select empno

      5      from emp inner

      6*     where inner.mgr = outer.empno)

    SQL> /

 

        EMPNO ENAME

    ---------- ----------

         7369 SMITH

         7499 ALLEN

         7521 WARD

         7654 MARTIN

         7844 TURNER

         7876 ADAMS

         7900 JAMES

         7934 MILLER

        

    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

      2  from dept d

      3  where not exists

      4      (select 1

      5       from emp e

      6*      where e.deptno = d.deptno)

     

       DEPTNO DNAME          LOC

    ---------- -------------- -------------

           40 OPERATIONS     BOSTON

      

    --IN与空值

           SQL> SELECT *

             2    FROM emp e

             3    WHERE e.empno NOT IN (

             4                           SELECT 7369 FROM dual

             5                           UNION ALL

             6                           SELECT NULL FROM dual

             7                          )

             8  ;

           

           EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

           ----- ---------- --------- ----- ----------- --------- --------- ------

           

           SQL> SELECT *

             2    FROM emp e

             3    WHERE e.empno IN ('7369',NULL)

             4  ;

           

           EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

           ----- ---------- --------- ----- ----------- --------- --------- ------

            7369 SMITH      CLERK      7902 1980-12-17     800.00               20

           

    

    /* 

    注：子查询要包含在括号内

        子查询一般放在比较条件的右侧

        除非进行TOP－N 分析，否则不要在子查询中使用ORDER BY。 */

 

    /*

八、多列子查询

    1、成对比较

    查询工资为部门最高的记录 */

    SQL> select * from scott.emp

      2  where (sal,job) in

      3     (select max(sal),job from scott.emp group by job);

   

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

         7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

         7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

         7839 KING       PRESIDENT            17-NOV-81       5000                    10

         7566 JONES      MANAGER         7839 02-APR-81       2975                    20

         7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

         7788 SCOTT      ANALYST         7566 19-APR-87       3000                   20   

      

       /*      

    2、非成对比较,实现了与上述类似的功能*/

    SQL> select * from scott.emp

      2  where sal in (select max(sal) from scott.emp group by job)

      3  and job in (select distinct job from scott.emp);

 

        EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------

         7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

         7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

         7566 JONES      MANAGER         7839 02-APR-81       2975                    20

         7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

         7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

         7839 KING       PRESIDENT            17-NOV-81       5000                    10

        

     /*

九、嵌套子查询

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

    SQL> select deptno,Num_emp

      2  from (select deptno,count(empno) as Num_emp from emp group by deptno) d

      3  where Num_emp > 3;

 

       DEPTNO    NUM_EMP

    ---------- ----------

           30          6

           20          5

      

    /*     

    注意：子查询对空值的处理

    除了count(*)外，都会忽略掉空值 */

 

    /*

 

连接查询：

 http://www.cnblogs.com/CareySon/archive/2010/04/13/1711095.html