多表的连接

多表连接：

SQL> select * from dept;

    DEPTNO DNAME                                      LOC
---------- ------------------------------------------ ---------------------------------------
        10 ACCOUNTING                                 NEW YORK
        20 RESEARCH                                   DALLAS
        30 SALES                                      CHICAGO
        40 OPERATIONS                                 BOSTON

SQL> select  * from emp;

     EMPNO ENAME                JOB               MGR HIREDATE               SAL       COMM     DEPTNO
---------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
      7890 zxw                  clerk            7902 11-NOV-80              900        100
      7892 test                 clerk            7906 22-NOV-82              860         50
      7369 SMITH                CLERK            7902 17-DEC-80              800                    20
      7499 ALLEN                SALESMAN         7698 20-FEB-81             1600        300         30
      7521 WARD                 SALESMAN         7698 22-FEB-81             1250        500         30
      7566 JONES                MANAGER          7839 02-APR-81             2975                    20
      7654 MARTIN               SALESMAN         7698 28-SEP-81             1250       1400         30
      7698 BLAKE                MANAGER          7839 01-MAY-81             2850                    30
      7782 CLARK                MANAGER          7839 09-JUN-81             2450                    10
      7788 SCOTT                ANALYST          7566 19-APR-87             3000                    20
      7839 KING                 PRESIDENT             17-NOV-81             5000                    10

     EMPNO ENAME                JOB               MGR HIREDATE               SAL       COMM     DEPTNO
---------- -------------------- ---------- ---------- --------------- ---------- ---------- ----------
      7844 TURNER               SALESMAN         7698 08-SEP-81             1500          0         30
      7876 ADAMS                CLERK            7788 23-MAY-87             1100                    20
      7900 JAMES                CLERK            7698 03-DEC-81              950                    30
      7902 FORD                 ANALYST          7566 03-DEC-81             3000                    20
      7934 MILLER               CLERK            7782 23-JAN-82             1300                    10

16 rows selected.

1.笛卡尔连接：


SQL> select count(1) from dept ,emp;

  COUNT(1)
----------
        64

这个数据没任何意议，没有连接条件；


2.内连接；

等值关系：
做多表连接，一定要找关联关系，主外键找关联关系；



SQL> select e.ename,e.deptno,d.dname from emp e,dept d
  2  where e.deptno=d.deptno;

ENAME                    DEPTNO DNAME
-------------------- ---------- ------------------------------------------
KING                         10 ACCOUNTING
CLARK                        10 ACCOUNTING
MILLER                       10 ACCOUNTING
FORD                         20 RESEARCH
SCOTT                        20 RESEARCH
ADAMS                        20 RESEARCH
JONES                        20 RESEARCH
SMITH                        20 RESEARCH
TURNER                       30 SALES
MARTIN                       30 SALES
WARD                         30 SALES

ENAME                    DEPTNO DNAME
-------------------- ---------- ------------------------------------------
ALLEN                        30 SALES
BLAKE                        30 SALES
JAMES                        30 SALES

14 rows selected.

----内连接，标准的SQL,支持所有的SQL
SQL> select e.ename,e.deptno,d.dname from emp e,dept d
  2  where e.deptno=d.deptno;
 
  也可写成：
 
           SQL> select e.ename,e.deptno,d.dname
           2     from emp e inner join dept d  on e.deptno=d.deptno;
        
         ENAME                    DEPTNO DNAME
         -------------------- ---------- ------------------------------------------
         KING                         10 ACCOUNTING
         CLARK                        10 ACCOUNTING
         MILLER                       10 ACCOUNTING
         FORD                         20 RESEARCH
         SCOTT                        20 RESEARCH
         ADAMS                        20 RESEARCH
         JONES                        20 RESEARCH
         SMITH                        20 RESEARCH
         TURNER                       30 SALES
         MARTIN                       30 SALES
         WARD                         30 SALES
        
         ENAME                    DEPTNO DNAME
         -------------------- ---------- ------------------------------------------
         ALLEN                        30 SALES
         BLAKE                        30 SALES
         JAMES                        30 SALES
        
         14 rows selected.



3.外连接；
    在外连接中，某些不满足条件的列也会显示出来。
    也就是说，要限制其中一个表的行，而不是限制另一个表的行。
    这种连接形式在许多情况下是非常有用的。
     在外连接中又分左连接和右连接
    
   A.左连接：
         ORACLE的写法：+号右边，以左边为准，右边补齐。
       左连接+在右边，左边的是主表，左边的表记录 全部显示，
       如果左边的表有记录，右边的表没有找到记录，则补NULL.
    



SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
  2  from  emp e, dept d 
  3  where e.deptno=d.deptno(+);
 
 
 
     SQL>  select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
                      from  emp e  left outer join  dept d 
                       on e.deptno=d.deptno;

ENAME      JOB               SAL     DEPTNO DNAME                LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MILLER     CLERK            1300         10 ACCOUNTING           NEW YORK
KING       PRESIDENT        5000         10 ACCOUNTING           NEW YORK
CLARK      MANAGER          2450         10 ACCOUNTING           NEW YORK
FORD       ANALYST          3000         20 RESEARCH             DALLAS
ADAMS      CLERK            1100         20 RESEARCH             DALLAS
SCOTT      ANALYST          3000         20 RESEARCH             DALLAS
JONES      MANAGER          2975         20 RESEARCH             DALLAS
SMITH      CLERK             800         20 RESEARCH             DALLAS
JAMES      CLERK             950         30 SALES                CHICAGO
TURNER     SALESMAN         1500         30 SALES                CHICAGO
BLAKE      MANAGER          2850         30 SALES                CHICAGO

ENAME      JOB               SAL     DEPTNO DNAME                LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MARTIN     SALESMAN         1250         30 SALES                CHICAGO
WARD       SALESMAN         1250         30 SALES                CHICAGO
ALLEN      SALESMAN         1600         30 SALES                CHICAGO
test       clerk             860                                              ----------多的
zxw        clerk             900                                            ---------多的   

16 rows selected.



   B.右连接：
         ORACLE的写法：+号左边，以右边为准，左边补齐。
       右连接+在左边，右边的是主表，右边的表记录 全部显示，
       如果右边的表有记录，左边的表没有找到记录，则补NULL.
      
      
      
      
      
       SQL>  select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
                        from  emp e  , dept d 
                       where e.deptno(+)=d.deptno;
 
 
 
         SQL>  select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
                      from  emp e  right outer join  dept d 
                       on e.deptno=d.deptno;
 
 

ENAME      JOB               SAL     DEPTNO DNAME                LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
KING       PRESIDENT        5000         10 ACCOUNTING           NEW YORK
CLARK      MANAGER          2450         10 ACCOUNTING           NEW YORK
MILLER     CLERK            1300         10 ACCOUNTING           NEW YORK
FORD       ANALYST          3000         20 RESEARCH             DALLAS
SCOTT      ANALYST          3000         20 RESEARCH             DALLAS
ADAMS      CLERK            1100         20 RESEARCH             DALLAS
JONES      MANAGER          2975         20 RESEARCH             DALLAS
SMITH      CLERK             800         20 RESEARCH             DALLAS
TURNER     SALESMAN         1500         30 SALES                CHICAGO
MARTIN     SALESMAN         1250         30 SALES                CHICAGO
WARD       SALESMAN         1250         30 SALES                CHICAGO

ENAME      JOB               SAL     DEPTNO DNAME                LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
ALLEN      SALESMAN         1600         30 SALES                CHICAGO
BLAKE      MANAGER          2850         30 SALES                CHICAGO
JAMES      CLERK             950         30 SALES                CHICAGO
       多的   ----------                       40 OPERATIONS           BOSTON

15 rows selected.
 
 C.自连接，左右都显示：
 
 
select e.ename, e.job,e.sal，d.deptno,d.dname,d.loc
from  emp e  full outer join  dept d
on e.deptno=d.deptno;
 
  SQL> select e.ename, e.job,e.sal,d.deptno,d.dname,d.loc
  2  from  emp e  full outer join  dept d
  3  on e.deptno=d.deptno;

ENAME      JOB               SAL     DEPTNO DNAME                LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MILLER     CLERK            1300         10 ACCOUNTING           NEW YORK
KING       PRESIDENT        5000         10 ACCOUNTING           NEW YORK
CLARK      MANAGER          2450         10 ACCOUNTING           NEW YORK
FORD       ANALYST          3000         20 RESEARCH             DALLAS
ADAMS      CLERK            1100         20 RESEARCH             DALLAS
SCOTT      ANALYST          3000         20 RESEARCH             DALLAS
JONES      MANAGER          2975         20 RESEARCH             DALLAS
SMITH      CLERK             800         20 RESEARCH             DALLAS
JAMES      CLERK             950         30 SALES                CHICAGO
TURNER     SALESMAN         1500         30 SALES                CHICAGO
BLAKE      MANAGER          2850         30 SALES                CHICAGO

ENAME      JOB               SAL     DEPTNO DNAME                LOC
---------- ---------- ---------- ---------- -------------------- ---------------------------------------
MARTIN     SALESMAN         1250         30 SALES                CHICAGO
WARD       SALESMAN         1250         30 SALES                CHICAGO
ALLEN      SALESMAN         1600         30 SALES                CHICAGO
test       clerk             860
zxw        clerk             900
                                         40 OPERATIONS           BOSTON

17 rows selected.
 
      

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27036311/viewspace-755748/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27036311/viewspace-755748/