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的右侧是空白，也就是没有老板。

从这里我们看到，当表中的某一个字段与这个表中另外字段的相关时，我们可能用到自连接。