Oracle表串連總結

標籤：

1 簡述

1) 兩個表的串連，是通過將一個表中的一列或者多列同另一個表中的列連結而建立起來的。用來串連兩張表的運算式組成了串連條件。當串連成功後，第二張表中的資料就同第一張表串連起來了，並形成了複合結果集

2) 有5種基本類型的的串連，內串連，外串連，自然串連，交叉串連，自串連。下面分別說下撒。

2 內串連（Inner Join / Join）

Inner join邏輯運算子返回滿足第一個（頂端）輸入與第二個（底端）輸入聯結的每一行。這個和用select查詢多表是一樣的效果，所以內串連用的很少。還有一點要說明的就是Join 預設就是inner join。 所以我們在寫內串連的時候可以省略inner 這個關鍵字。註：等同於最常見的等值串連

下面舉例說明一下內串連：

Ø 標準寫法: inner join

SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO

Ø 這裡省略了inner關鍵字

SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO

Ø select 多表查詢

SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO

執行結果如下：

從這三個SQL 的結果我們也可以看出，他們的作用是一樣的。

3 外串連（Out Join）

outer join則會返回每個滿足第一個（頂端）輸入與第二個（底端）輸入的聯結的行。它還返回任何在第二個輸入中沒有匹配行的第一個輸入中的行。外串連分為三種： 左外串連，右外串連，全外串連。 對應SQL：LEFT/RIGHT/FULL OUTER JOIN。 通常我們省略outer 這個關鍵字。 寫成：LEFT/RIGHT/FULL JOIN。

在左外串連和右外串連時都會以一張表為基表，該表的內容會全部顯示，然後加上兩張表匹配的內容。 如果基表的資料在另一張表沒有記錄。 那麼在相關聯的結果集行中列顯示為空白值（NULL）。

對於外串連， 也可以使用“(+) ”來表示。 關於使用（+）的一些注意事項：
1.（+）操作符只能出現在where子句中，並且不能與outer join文法同時使用。
2.（+）操作符執行外串連時，如果在where子句中包含有多個條件，則必須在所有條件中都包含（+）操作符
3.（+）操作符只適用於列，而不能用在運算式上。
4.（+）操作符不能與or和in操作符一起使用。
5.（+）操作符只能用於實現左外串連和右外串連，而不能用於實現完全外串連。

3.1 左外串連（Left outer join / left join）

左外串連就是在等值串連的基礎上加上主表中的未匹配資料，left join是以左表的記錄為基礎的,樣本中EMP可以看成左表,DEPT可以看成右表,它的結果集是EMP表中的資料，在加上EMP表和DEPT表匹配的資料。換句話說,左表(EMP)的記錄將會全部表示出來,而右表(DEPT)只會顯示符合搜尋條件的記錄。DEPT表記錄不足的地方均為NULL.

SELECT * FROM emp LEFT JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO

用（+）來實現， 這個+號可以這樣來理解： + 表示補充，即哪個表有加號，這個表就是匹配表。所以加號寫在右表，左表就是全部顯示，故是左串連。注意： 用（+） 就要用關鍵字where

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

3.3 右外串連（right outer join / right join）

右外串連是在等值串連的基礎上加上被串連表的不匹配資料和left join的結果剛好相反,是以右表(BL)為基礎的, 顯示BL表的所以記錄，在加上Dave和BL 匹配的結果。 Dave表不足的地方用NULL填充.

SELECT * FROM emp RIGHT JOIN dept ON EMP.DEPTNO = DEPT.DEPTNO

?用（+）來實現， 這個+號可以這樣來理解： + 表示補充，即哪個表有加號，這個表就是匹配表。所以加號寫在左表，右表就是全部顯示，故是右串連。

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

3.4 全外串連（full outer join / full join）

左表和右表都不做限制，所有的記錄都顯示，兩表不足的地方用null 填充。 全外串連不支援（+）這種寫法。

SELECT * FROM EMP FULL JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO

4.自然串連

自然串連是在廣義笛卡爾積R×S中選出同名屬性上符合相等條件元組，再進行投影，去掉重複的同名屬性，組成新的關係。即 自然串連是在兩張表中尋找那些資料類型和列名都相同的欄位，然後自動地將他們串連起來，並返回所有符合條件按的結果。

SELECT * FROM EMP NATURAL JOIN DEPT

註：不能加限定條件，否則會報錯

SQL 錯誤: ORA-25155: NATURAL 聯結中使用的列不能有限定詞25155. 00000 -  "column used in NATURAL join cannot have qualifier"*Cause:    Columns that are used for a named-join (either a NATURAL join           or a join with a USING clause) cannot have an explicit qualifier.*Action:   Remove the qualifier.

5.交叉串連

交叉串連不帶ON子句，它返回被串連的兩個表所有資料行的笛卡爾積，返回到 結果集合中的資料行數等於第一個表中符合查詢條件的資料行數乘以第二個表中符合查 詢條件的資料行數。

SELECT * FROM emp CROSS JOIN dept;

等價於

SELECT * FROM emp,dept;

6.自串連

串連的表是同一張表，使用自串連可以將自身表的一個鏡像當作另一個表來對待，從而能夠得到一些特殊的資料。

在oracle的scott的schema中有一個表是emp。在emp中的每一個員工都有自己的mgr(經理)，並且每一個經理自身也是公司的員工，自身也有自己的經理。

但現在我們只有一張emp表。所以我們可以採用自串連。自串連的本意就是將一張表看成多張表來做串連。我們可以這樣來寫SQL語句:

select work.ename worker,mgr.ename from scott.emp work, scott.emp mgr where work.mgr = mgr.empno order by work.ename;

各種串連的一個圖示：

Oracle表串連總結