正確加入

RIGHT OUTER JOIN 在兩個表之間執行連線,這兩個表需要顯式連線子句,但不排除第二個表中不匹配的行。

例:

SELECT                                      
      ENAME,                                
      DNAME,                                
      EMP.DEPTNO,                           
      DEPT.DEPTNO                           
FROM                                        
      SCOTT.EMP RIGHT OUTER JOIN SCOTT.DEPT  
      ON EMP.DEPTNO = DEPT.DEPTNO;          

由於包含了無法匹配的 SCOTT.DEPT 行,但是 SCOTT.EMP 的行不匹配,上述內容相當於使用 LEFT OUTER JOIN 的以下語句。

SELECT                                      
      ENAME,                                
      DNAME,                                
      EMP.DEPTNO,                           
      DEPT.DEPTNO                           
FROM                                        
      SCOTT.DEPT RIGHT OUTER JOIN SCOTT.EMP  
      ON DEPT.DEPTNO = EMP.DEPTNO;

以下是兩個表之間的右外連線示例:

樣本表: 員工

+-----------+---------+
|    NAME   |  DEPTNO |
+-----------+---------+
|     A     |    2    |
|     B     |    1    |
|     C     |    3    |
|     D     |    2    |
|     E     |    1    |
|     F     |    1    |
|     G     |    4    |
|     H     |    4    |
+-----------+---------+

樣本表: DEPT

+---------+--------------+
|  DEPTNO |   DEPTNAME   |
+---------+--------------+
|    1    |  ACCOUNTING  |
|    2    |    FINANCE   |
|    5    |   MARKETING  |
|    6    |      HR      |
+---------+--------------+

現在,如果執行查詢:

SELECT
      *
FROM
      EMPLOYEE RIGHT OUTER JOIN DEPT
      ON EMPLOYEE.DEPTNO = DEPT.DEPTNO;

輸出:

+-----------+---------+---------+--------------+
|    NAME   |  DEPTNO |  DEPTNO |   DEPTNAME   |
+-----------+---------+---------+--------------+
|     A     |    2    |    2    |    FINANCE   |
|     B     |    1    |    1    |  ACCOUNTING  |
|     D     |    2    |    2    |    FINANCE   |
|     E     |    1    |    1    |  ACCOUNTING  |
|     F     |    1    |    1    |  ACCOUNTING  |
|           |         |    5    |   MARKETING  |
|           |         |    6    |      HR      |
+-----------+---------+---------+--------------+

與查詢等價的 Oracle(+)語法是:

SELECT *
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNO(+) = DEPT.DEPTNO;