網頁

2018/12/1

Oracle INNER JOIN語法 oracle inner join syntax

Oracle資料庫inner join用法如下。

例如有兩張資料表DEPARTMENTEMPLOYEE

 EMPLOYEE                             DEPARTMENT
+----+---------------+------+        +---------------+-----------------+
| ID | DEPARTMENT_ID | NAME |        | DEPARTMENT_ID | DEPARTMENT_NAME |
+----+---------------+------+        +---------------+-----------------+
|  1 |             1 | John |        |             1 |           SALES |
|  2 |             1 | Mary |        |             2 |              HR |
|  3 |             2 | Luke |        +---------------+-----------------+
|  4 |             2 | Anna |
|  5 |             3 | Eric |
+----+---------------+------+

則對此兩張表依DEPARTMENT_ID做inner join如下。

SELECT e.ID, e.NAME, d.DEPARTMENT_NAME 
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;

查詢結果僅會返回兩表DEPARTMENT_ID相等的列(row)。

+----+------+-----------------+
| ID | NAME | DEPARTMENT_NAME |
+----+------+-----------------+
|  1 | John | SALES           |
|  2 | Mary | SALES           |
|  3 | Luke | HR              |
|  4 | Anna | HR              |
+----+------+-----------------+

EMPLOYEE第5列的DEPARTMENT_ID為3不存在於DEPARTMENT.DEPARTMENT_ID,不符inner join條件所以未查出。


JOIN前面沒有指示是哪一種join type(INNER, OUTER, LEFT, RIGHT)等時是INNER JOIN(其他資料庫的SQL語法也是一樣,因為這是ANSI SQL標準)。例如下面也是INNER JOIN

SELECT e.ID, e.NAME, d.DEPARTMENT_NAME 
FROM employee e
JOIN department d
ON e.department_id = d.department_id;

在Oracle 9i以前inner join的舊語法如下,直接查詢多個表並以WHERE來篩選相等的欄位;之後改用以上INNER JOIN較清楚的寫法。

SELECT e.ID, e.NAME, d.DEPARTMENT_NAME
FROM employee e, department d
WHERE e.department_id = d.department_id;


沒有留言:

張貼留言