Oracle 外连接和（+）号的用法

骑猪看日落 2022-07-26 04:12 175阅读 0赞

转自： [http://anjian0802.blog.163.com/blog/static/47988453201031803577/][http_anjian0802.blog.163.com_blog_static_47988453201031803577]  
对于外连接，Oracle中可以使用“(+)”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面将配合实例一一介绍。  
1. LEFT OUTER JOIN：左外关联

SELECT  e.last\_name, e.department\_id, d.department\_name   
FROM  employees e   
 LEFT   OUTER   JOIN  departments d   
ON  (e.department\_id  =  d.department\_id);

等价于

SELECT  e.last\_name, e.department\_id, d.department\_name   
FROM  employees e, departments d   
WHERE  e.department\_id = d.department\_id( \+ );

结果为：所有员工及对应部门的记录，包括没有对应部门编号department\_id的员工记录。   
  
2. RIGHT OUTER JOIN：右外关联

SELECT  e.last\_name, e.department\_id, d.department\_name   
FROM  employees e   
 RIGHT   OUTER   JOIN  departments d   
ON  (e.department\_id  =  d.department\_id);

等价于

SELECT  e.last\_name, e.department\_id, d.department\_name   
FROM  employees e, departments d   
WHERE  e.department\_id( \+ ) = d.department\_id;

结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。   
  
3. FULL OUTER JOIN：全外关联

SELECT  e.last\_name, e.department\_id, d.department\_name   
FROM  employees e   
FULL   OUTER   JOIN  departments d   
ON  (e.department\_id  =  d.department\_id);

结果为：所有员工及对应部门的记录，包括没有对应部门编号 department\_id的员工记录和没有任何员工的部门记录。

外连接:   
除了显示匹配相等连接条件的数据外,还可以显示某一个表中无法匹配相等连接条件的记录!   
\------------------------------------------------   
1) 左条件(+) = 右条件   
左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!   
也称为右外连接.   
\--------------------------------   
可以用下列语句取代:   
SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;   
2) 左条件 = 右条件(+)   
右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!   
也称为左外连接.   
\--------------------------------   
可以用下列语句取代:   
SELECT...FROM 表1 LEFT OUTER JOIN 表2 ON 条件;   
  
REM 除了相等连接之外,显示没有员工的部门信息.   
SELECT \* FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;   
SELECT \* FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;   
REM 除了相等连接之外,显示没有部门的员工信息.   
SELECT \* FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);   
SELECT \* FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

Oracle 建议你用在From语句后用Outer Join语法 而不是Oracle的Join操作符(+).而且(+)是要受下面的规则限制的，但Outer Join语法则不受的～～  
  
1）你不可以在查询块中使用(+) 当它同时包含 join的from语句中   
2）(+)只是在where语句中,并且只能对应一个表或视图的一行字段   
3）如果A和B做联接时有多个条件，那么(+)必须完善所有的匹配条件，   
如果没有 ,oracle不会警告你～只是结果自然不同的   
4）不可以在作出一个表外查询 另张表内查询的(+)联接操作～～   
5）不可以用（+）外联接到自己 当然Self Join是可以的   
6）含（+）的Where后的注意   
OR不可用   
IN不可用   
子查询不可用   
  
以下给些个例子：

SQL co de

SQL> desc part
    
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------
     PART_ID                                   NOT NULL VARCHAR2(4)
     SUPPLIER_ID                                        VARCHAR2(4)
    
    SQL> select * from part;
    
    PART SUPP
    ---- ----
    P1   S1
    P2   S2
    P3
    P4
    
    SQL> desc supplier
    
     Name                                      Null?    Type
     ----------------------------------------- -------- -----------------
     SUPPLIER_ID                               NOT NULL VARCHAR2(4)
     SUPPLIER_NAME                             NOT NULL VARCHAR2(20)
    
    SQL> select * from supplier;
    
    SUPP SUPPLIER_NAME
    ---- --------------------
    S1   Supplier#1
    S2   Supplier#2
    S3   Supplier#3
    
    SQL> select p.part_id, s.supplier_name
      2  from part p, supplier s
      3  where p.supplier_id = s.supplier_id (+);
    
    PART SUPPLIER_NAME
    ---- --------------------
    P1   Supplier#1
    P2   Supplier#2
    P3
    P4
    
    --(+)是单向的
    
    SQL> select p.part_id, s.supplier_name
      2  from part p, supplier s
      3  where p.supplier_id (+) = s.supplier_id (+);
    where p.supplier_id (+) = s.supplier_id (+)
                            *
    ERROR at line 3:
    ORA-01468: a predicate may reference only one outer-joined table
    
    --实现Full Join的方法
    
    SQL> select p.part_id, s.supplier_name
      2  from part p, supplier s
      3  where p.supplier_id = s.supplier_id (+)
      4  union
      5  select p.part_id, s.supplier_name
      6  from part p, supplier s
      7  where p.supplier_id (+) = s.supplier_id;
    
    PART SUPPLIER_NAME
    ---- --------------------
    P1   Supplier#1
    P2   Supplier#2
    P3
    P4
         Supplier#3
    
    --现在的语法 
    SQL> select p.part_id, s.supplier_name
      2  from part p full outer join supplier s
      3  on p.supplier_id = s.supplier_id;
    
    PART SUPPLIER_NAME
    ---- --------------------
    P1   Supplier#1
    P2   Supplier#2
    P4
    P3
         Supplier#3
    
    另外的EG： 
    
    SQL code
    SQL> select * from testa;
    
            ID NAME  ADDR         AGE
    ---------- ------ ------ ----------
             3 电子   南京           23
             5 物理   北京           23
             6 物理   河北           25
             1 电脑   上海           23
             2 电子   北京           22
             4 物理   芜湖           22
             7 艺术   上海           21
             8 歌剧   苏州           16
             9 沈亮   上海           22
    
    已选择9行。
    
    SQL> select * from testb;
    
            ID ADDR
    ---------- ----------
            7 上海
             1 北京
             3 上海
             5 合肥
    --匹配完全
    select testa.id,testa.addr from testa,testb where testa.id(+)=testb.id and
    testa.addr(+)=testb.addr;
    
            ID ADDR
    ---------- ------
             7 上海

[http_anjian0802.blog.163.com_blog_static_47988453201031803577]: http://anjian0802.blog.163.com/blog/static/47988453201031803577/