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列很麻煩 

 

附上網上相關資源,供擴充套件閱讀: