数据库中join的类型

join类型总览

为了演示他们的区别，我们构建两个表，员工表emp，部门表dept，员工表中有21条记录，其中19条员工记录是有部门可查的，部门编号能对应部门表中的记录，有一条员工记录没有部门，还有一条记录的部门在部门表中不存在deptid=6，即查不到。 另外，部门表中有6条记录，代表六个部门，表结构和表数据如下所示：

内连接inner join

INNER JOIN 产生的结果是AB的交集。

用法

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

INNER JOIN 与 JOIN 是相同的。

实例

直接使用内连接查询，我们只能查询到19条符合条件的记录，如下所示：

左外连接LEFT [OUTER] JOIN

LEFT [OUTER] JOIN 产生左边表的完全集，而右边表中匹配的则有值，没有匹配的则以null值取代。

用法

SELECT column_name(s)
FROM table_name1
LEFT OUTER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

实例

emp表有21条记录，以左表作参照，这里把员工信息全部查询出来，当deptid=null和deptid=6的时候，右表中无记录，这里就用NULL表示。

右外连接RIGHT [OUTER] JOIN

RIGHT [OUTER] JOIN 产生右边表的完全集，而左边表中匹配的则有值，没有匹配的则以null值取代。

用法

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

实例

右连接查询的时候，以右表为参照，将右表中所有数据都查询出来了，左表中没有对应记录的就用NULL表示。

全连接FULL [OUTER] JOIN

FULL [OUTER] JOIN 产生A和B的并集。对于没有匹配的记录，则会以null值。

用法

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name 

交叉连接CROSS JOIN

CROSS JOIN 把两个表的数据进行一个NM的组合，即笛卡尔积。如本例会产生35=15条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

用法

SELECT column_name(s)
FROM table_name1
CROSS JOIN table_name2 

自然连接NATURE JOIN

NATURE JOIN是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。

用法

SELECT   SNAME, DNAME, CNO, TNAME
FROM   STUDENT 
NATURAL JOIN TEACHER 

等同于

SELECT   SNAME, S.DNAME, S.CNO, TNAME
FROM   STUDENT AS S, TEACHER AS T
WHERE  S.DNAME = T.DNAME
AND   S.CNO = T.CNO

但是，与自然连接不同，采用这种方式进行表的连接，虽然两表的CNO和DNAME列相等，但它们在结果表中出现了两次

自连接SELF JOIN

自连接的本意就是将一张表看成多张表来做连接。我们什么时候应该使用自连接呢？我们来看下面的例子。

在oracle的scott的schema中有一个表是emp，在emp中的每一个员工都有自己的mgr(经理)，并且每一个经理自身也是公司的员工，自身也有自己的经理。下面我们需要将每一个员工自己的名字和经理的名字都找出来。这时候我们该怎么做呢？

如果我们有两张这样的表分别叫worker和mgr，那么我们就很好写SQL语句。

Select worker.name,Mgr.name
From worker,mgr
Where worker.mgr = mgr.id;

但现在我们只有一张表。你也许说我们现在再建一张表，把同样的数据拷贝过去不就可以了吗？是的，这样可以，但我们不会采用，因为这样就会很麻烦，而且数据严重冗余等等很多弊端。
我们有更好的方法，那就是自连接，我们可以这样来写SQL语句

Select work.ename '工人', 'works for',mgr.ename '老板'
From emp work, emp mgr
Where work.mgr = mgr.empno
Order by work.ename;

左连接，右连接，内连接和全外连接的区别

• left join（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。
• right join（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。
• inner join（等值连接或者叫内连接）：只返回两个表中连接字段相等的行。
• full join（全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。