oracle sql右连接,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列很麻烦

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