Join操作

Join操作基本分为3大类：外连接(细分为：左连接、右连接、全连接)、自然连接、内连接
Join操作的共性：第一步均为将所有参与操作的表进行了一个笛卡儿积，然后才依据各连接条件进行记录的筛选
 
SQL> select * from employees;
LAST_NAME                             DEPARTMENT_ID       SALARY
------------------------------            ------------------------         ------------
Getz                                           10                                     3000
Davis                                         20                                     1500
King                                           20                                     2200
Davis                                         30                                     5000
Kochhar                                                                              5000
 
SQL> select * from departments;
DEPARTMENT_ID             DEPARTMENT_NAME
------------------------              ------------------------------
                10                         Sales
                20                         Marketing
                30                         Accounts
                40                         Administration
 
--------------------------------------------------Left outer join--------------------------------------------------
SQL> select * from employees e left outer join departments d
 2 on e.department_id=d.department_id;
LAST_NAME        DEPARTMENT_ID          SALARY           DEPARTMENT_ID                 DEPARTMENT_NAME
-----------------         ------------------------           -----------           -----------------------                    ------------------------------
Getz                       10                                       3000                 10                                             Sales
King                       20                                       2200                 20                                             Marketing
Davis                     20                                       1500                 20                                             Marketing
Davis                     30                                       5000                 30                                             Accounts
Kochhar                                                           5000
附加：Oracle9i以前版本中左连接的写法如下：
SQL> select * from employees e ，departments d
 2  on e.department_id（+）=d.department_id;
 
-------------------------------------------------Right outer join-------------------------------------------------
SQL> select * from employees right outer join departments
 2 using(department_id);
DEPARTMENT_ID    LAST_NAME       SALARY        DEPARTMENT_NAME
-----------------------     -----------------        ----------         ------------------------------
                          10     Getz                      3000             Sales
                          20     Davis                   1500              Marketing
                          20     King                     2200              Marketing
                          30     Davis                   5000              Accounts 
                          40                                                                                      Administration
附加：Oracle9i以前版本中左连接的写法如下：
SQL> select * from employees e ，departments d
 2 where e.department_id=d.department_id(+);
 
--------------------------------------------------Full outer join--------------------------------------------------
SQL> select * from employees full outer join departments
 2 using(department_id);
 
DEPARTMENT_ID         LAST_NAME        SALARY         DEPARTMENT_NAME
------------------------          -------------------       ----------          -----------------------------
                           10          Getz                        3000             Sales
                           20          King                       2200             Marketing
                           20          Davis                     1500             Marketing
                           30          Davis                     5000             Accounts
                                          Kochhar                5000
                          40                                                                 Administration
说明：[1]外连接必须使用on或using子句提供相应的连接条件
             [2]不能为using子句中所列举的列指定表别名，即使在group by和select子句中也是如此
             [3]外连接规则：左连右补，右连左补，全连左右合并
             如：对表departments表进行右连接时，在两表完成笛卡儿积后再依据连接条件using(department_id)来筛选两表中department_id值相同的记录，但对DEPARTMENT_ID=40时employees表中没有与之匹配的记录，按常理此DEPARTMENT_ID=40所对应的记录将被抛弃，但就是为了保全连接表（departments表）中的所有记录就必须在employees表中虚拟出一条与之匹配的记录来保全连接表的所有记录，当然这条虚拟的记录显示时值全为null
 
--------------------------------------------------Natural join--------------------------------------------------
SQL> select * from employees natural join departments;
 
DEPARTMENT_ID   LAST_NAME        SALARY        DEPARTMENT_NAME
------------------------   ------------------        ----------          ------------------------------
                            10   Getz                       3000             Sales
                            20   Davis                     1500             Marketing
                            20   King                       2200             Marketing
                            30   Davis                     5000             Accounts
说明：自然连接是通过对参与表关系中所有同名的属性对取等（即相等比较）来完成的，故无须自己添加连接条件与外连接的区别在于对于无法匹配的记录外连接会虚拟一条与之匹配的记录来保全连接表中的所有记录，但自然连接不会
--------------------------------------------------Inner join--------------------------------------------------
SQL> select * from employees inner join departments
 2 using(department_id);
 
DEPARTMENT_ID   LAST_NAME        SALARY        DEPARTMENT_NAME
------------------------   -----------------         ----------         ------------------------------
                           10   Getz                        3000            Sales
                           20   Davis                      1500            Marketing
                           20   King                        2200            Marketing
                           30   Davis                      5000            Accounts
说明：内连接与自然连接基本相同，不同之处在于自然连接只能是同名属性的等值连接，而内连接可以使用using或on子句来指定连接条件

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/cowbane/archive/2008/01/30/2072288.aspx