oracle sql内连接_左(右)连接_全外连接_几种写法

----创建测试表
SQL> desc t_pkg;
Name       Type          Nullable Default Comments
---------- ------------- -------- ------- --------
ID         INTEGER       Y                        
ORDER_ID   VARCHAR2(100) Y                        
COMP_TYP   VARCHAR2(100) Y                        
COMP_MONEY NUMBER        Y  

----插入数据至测试表
SQL> select * from t_pkg;
 
                                     ID ORDER_ID                                                                         COMP_TYP                                                                         COMP_MONEY
--------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                      1 order_1                                                                          tele                                                                                    100
                                      2 order_1                                                                          seller                                                                                   80
                                      3 order_2                                                                          seller                                                                                  900
                                      4 order_3                                                                          tele                                                                                    700
 
 
----如下sql查询(全外连接),3种情况:
--1种情况:有tele和seller
--2种情况:仅有tele
--3种情况:仅有seller
SQL> select
  2  nvl(t_pkg1.id,t_pkg2.id) as id,
  3  nvl(t_pkg1.order_id,t_pkg2.order_id) as order_id,
  4  t_pkg1.comp_money as comp_money_1,
  5  t_pkg2.comp_money as comp_money_2
  6  from (select id,order_id,comp_money from t_pkg where comp_typ='tele') t_pkg1
  7        full outer join
  8       (select id,order_id,comp_money from t_pkg where comp_typ='seller') t_pkg2
  9  on (t_pkg1.order_id=t_pkg2.order_id)
 10  ;
 
        ID ORDER_ID                                                                         COMP_MONEY_1 COMP_MONEY_2
---------- -------------------------------------------------------------------------------- ------------ ------------
         1 order_1                                                                                   100           80
         3 order_2                                                                                                900
         4 order_3                                                                                   700

小结:1,全外连接的写法full outer  join或full join,条件用on
     2,因为全外连接产生的结果可能列值为null,如果产生的结果集是中间结果集,还需要进一步处理,可以用nvl对此中间结果集的列(一般是关联列)进行转化,毕竟关联null列很麻烦 

 

附上网上相关资源,供扩展阅读:

http://www.blogjava.net/hello-yun/archive/2011/04/08/347890.html

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

转载于:http://blog.itpub.net/9240380/viewspace-753529/