Oracle資料庫inner join用法如下。
例如有兩張資料表DEPARTMENT及EMPLOYEE。
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;
沒有留言:
張貼留言