Outer join 原理

       在9i之前，Oracle使用了outer join 操作符 ‘+’来实现外连接，但是这个’+’语法只支持 left/right outer join,不支持full outer join,要实现full outer join可以通过union all 实现。而且select * from a,b where a.id=b.id(+)，这是会报错的，因为老语法连接条件不能用OR组合。

       Outer join有基表和从表的概念。基表的内容会在outer join里全部选中，然后基表根据join的条件到从表中选出从表记录，如果满足条件则按从表时间内容选出，否则没有找到则从表的对应行的所有列值为NULL。

       Outer join分为3类：左外连接(left outer join)、右外连接(right outer join)、全外连接(full outer join)。左外连接就是left outer join 左边的表示基表，右边的是从表，full outer join 比较特殊，在它左边和右边的表互为基表和从表的，比如A full outer join B，可以简单理解为先以A为基表，B为从表选出结果，然后以B为基表，A为从表选出结果，然后最后结果剔除重复在两个步骤中都出现的结果（不是剔除重复的行）。老语法只支持左外连接和右外连接，在where条件里无+号的那端表达式中的列所属表为基表，有+的表达式中的列所属表为从表，比如 where a.id = b.id(+),这个是b表示从表，a表示基表。

createtable a as select level id,'x'||level name
    from dual connect by level<5
   union all
    select level,'y'||level
    from dual connect by level<5;
   
create table b as select level id,'x'||level name
    from dual connect by level<3;

SQL> select * from a;

 

        ID NAME

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

         1 x1

         2 x2

         3 x3

         4 x4

         1 y1

         2 y2

         3 y3

         4 y4

 

8 rows selected

 

SQL> select * from b;

        ID NAME

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

         1 x1

         2 x2

SQL> select * from a,b  where a.id = b.id(+) and a.name like 'x%';

        ID NAME                                              ID NAME

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

         1 x1                                                 1 x1

         2 x2                                                 2 x2

         4 x4                                                  

         3 x3                                                  

 

SQL> select * from a left join b on a.id =b.id and a.name like 'x%';

        ID NAME                                              ID NAME

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

         1 x1                                                 1 x1

         2 x2                                                 2 x2

         3 x3                                                  

         4 x4                                                  

         1 y1                                                   

         2 y2                                                  

         3 y3                                                  

         4 y4                                                  

8 rows selected

SQL> select * from a left join b on a.id =b.id  where a.name like 'x%';

        ID NAME                                              ID NAME

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

         1 x1                                                 1 x1

         2 x2                                                 2 x2

         4 x4                                                  

         3 x3                                                  

SQL> select * from a left join b on a.name like 'x%' where a.id=b.id;

        ID NAME                                              ID NAME

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

         1 x1                                                 1 x1

         2 x2                                                 2 x2

SQL> select * from a full join  b on a.name like 'x%' ;

 

        ID NAME                                              ID NAME

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

         1 x1                                                 1 x1

         1 x1                                                 2 x2

         2 x2                                                 1 x1

         2 x2                                                 2 x2

         3 x3                                                 1 x1

         3 x3                                                 2 x2

         4 x4                                                 1 x1

         4 x4                                                 2 x2

         1 y1                                                  

         2 y2                                                   

         3 y3                                                  

         4 y4                                                  

12 rows selected