Oracle中的自串連(self join)

自串連(self join)是SQL語句中經常要用的串連方式，使用自串連可以將自身表的一個鏡像當作另一個表來對待，從而能夠得到一些特殊的資料。

我們什麼時候應該使用自串連呢？我們來看下面的例子。

在oracle的scott的schema中有一個表是emp

在emp中的每一個員工都有自己的mgr(經理)，並且每一個經理自身也是公司的員工，自身也有自己的經理。下面我們需要將每一個員工自己的名字和經理的名字都找出來。這時候我們該怎麼做呢？

如果我們有兩張這樣的表分別教worker和mgr，那麼我們就很好寫SQL語句。

Select worker.name,

Mgr.name

From worker,mgr

Where worker.id = mgr.id;

但現在我們只有一張表。你也許說我們現在在建一張表，把同樣的資料拷貝過去不就可以了嗎？是的，這樣可以，但我們不會採用，因為這樣就會很麻煩，而且資料嚴重冗餘等等很多弊端。

我們有更好的方法，那就是自串連。

自串連的本意就是將一張表看成多張表來做串連。我們可以這樣來寫SQL語句

select work.ename “工人”,’ works for’,mgr.ename “老闆”

from emp work, emp mgr

where work.mgr = mgr.empno

order by work.ename;

得到了如下結果:

工人 ‘WORKSFOR’ 老闆

——————– ——————– ——————–

ADAMS works for SCOTT

ALLEN works for BLAKE

BLAKE works for KING

CLARK works for KING

FORD works for JONES

JAMES works for BLAKE

JONES works for KING

MARTIN works for BLAKE

MILLER works for CLARK

SCOTT works for JONES

SMITH works for FORD

TURNER works for BLAKE

WARD works for BLAKE

這裡我們注意到,King這個人沒有出現在工人列裡面，是因為這個人是整個公司的老闆，他沒有經理。如果這時候我們希望king這個人也出現在左側，即使他沒有老闆。

我們可以使用剛剛才介紹過的oracle中外串連

例如：

select work.ename “工人”,’ works for’,mgr.ename “老闆”

from emp work, emp mgr

where work.mgr = mgr.empno (+)

order by work.ename;

得到結果：

工人 ‘WORKSFOR’ 老闆

——————– ——————– ———-

ADAMS works for SCOTT

ALLEN works for BLAKE

BLAKE works for KING

CLARK works for KING

FORD works for JONES

JAMES works for BLAKE

JONES works for KING

KING works for

MARTIN works for BLAKE

MILLER works for CLARK

SCOTT works for JONES

SMITH works for FORD

TURNER works for BLAKE

WARD works for BLAKE

這是我們看到king的右側是空白，也就是沒有老闆。

從這裡我們看到，當表中的某一個欄位與這個表中另外欄位的相關時，我們可能用到自串連。