oracle 外连接(outer join)

rem 一半情况下的连接
SQL> select INFO.SID, INFO.AGE, INFO.NAME, WORK.COMPANY, WORK.ADDRESS
  2  from TEST_INFO INFO , TEST_WORK WORK
  3  where INFO.SID = WORK.SID;

       SID        AGE NAME         COMPANY         ADDRESS                     
---------- ---------- ------------ --------------- ---------------             
      1001         28 Jack         MS              US                          
      1002         33 Micky        Oracle          CHINA                       
      1003         43 Kent         HP              US                          

rem 右连接
SQL> select INFO.SID, INFO.AGE, INFO.NAME, WORK.COMPANY, WORK.ADDRESS
  2  from TEST_INFO INFO right outer join TEST_WORK WORK
  3  on INFO.SID = WORK.SID;

       SID        AGE NAME         COMPANY         ADDRESS                     
---------- ---------- ------------ --------------- ---------------             
      1001         28 Jack         MS              US                          
      1002         33 Micky        Oracle          CHINA                       
      1003         43 Kent         HP              US                          
                                   CANON           JAP                         
                                   SONY            JAP                         
                                   Lenovo          CHINA                       
                                   Apple           US                          

rem 左连接
SQL> select INFO.SID, INFO.AGE, INFO.NAME, WORK.COMPANY, WORK.ADDRESS
  2  from TEST_INFO INFO left outer join TEST_WORK WORK
  3  on INFO.SID = WORK.SID;

       SID        AGE NAME         COMPANY         ADDRESS                     
---------- ---------- ------------ --------------- ---------------             
      1001         28 Jack         MS              US                          
      1002         33 Micky        Oracle          CHINA                       
      1003         43 Kent         HP              US                          
      1004         32 Hence                                                    

rem 全连接
SQL> select INFO.SID, INFO.AGE, INFO.NAME, WORK.COMPANY, WORK.ADDRESS
  2  from TEST_INFO INFO full outer join TEST_WORK WORK
  3  on INFO.SID = WORK.SID;

       SID        AGE NAME         COMPANY         ADDRESS                     
---------- ---------- ------------ --------------- ---------------             
      1001         28 Jack         MS              US                          
      1002         33 Micky        Oracle          CHINA                       
      1003         43 Kent         HP              US                          
      1004         32 Hence                                                    
                                   CANON           JAP                         
                                   SONY            JAP                         
                                   Lenovo          CHINA                       
                                   Apple           US                          

已选择8行。

SQL> spool off