笛卡尔积、交叉连接、内连接、外连接

 

导 语

关联多个表查找数据的本质是： 先做笛卡尔积（也叫交叉连接），再选取符合条件的行

参考 http://www.360doc.com/content/14/1229/21/7635_436727229.shtml

 

 

 

基于上述表A、B的交叉连接语句是： select * from A,B;  结果如下图，共5*5=25条记录：

 

也可以用select * from B,A; 结果如下：

 

由于只是集合元素间的简单组合，大部分组合是没有意义的。所以需要加条件。比如常见的 A.id=B.id。当然也可以是A.id>B.id，虽然大多数情况下这样的比较没意义。

 

select * from B,A where A.id=B.id; 结果如下：

select * from A,B where A.id>B.id; 结果如下：

 

实例（一）

下图是一个员工信息表EMP。

EMPNO: 编号
ENAME: 姓名
JOB: 职务
MGR: 员工的老板(Manager)的编号


                                 图 (一)

上图中的MGR字段显示的是员工编号，为了把姓名显示出来，我们发出一条SQL语句，得到下图所示结果：


                                                                       图 (二)

内连接INNER JOIN的功能与图二中SQL语句的功能一模一样。如下图：


                                                                            图 (三）

我们发现图二、图三只有13条记录，比图一少了一条记录。是什么原因呢？

原因是INNER JOIN这个方法只选择出满足匹配条件的记录。在这里，匹配条件是e.MGR=b.EMPNO，因为是EMP表与本身作连接，取别名e和b以作区分。

从图一可以看出KING的MGR字段为空，所以没有显示KING的信息。另一种情况是MGR字段的值不在EMPNO范围内时，也不会显示。

下图是改变员工ALLEN的MGR字段值为0000后的查询结果，其中KING与ALLEN的信息都不在查询结果之列。


                                                                       图 (四)

接下来，也许你希望显出所有员工的信息，不管他是不是有老板，即如果有就显示出来，没有就空着。

于是我想到了用LEFT OUTER JOIN语句。当 表A LEFT OUTER JOIN 表B 时，表A中的所有记录会显示出来，包括满足匹配条件与不满足匹配条件的记录。于是重新得到了14个员工的信息，如下图所示。

它与图一的区别是MGR字体显示的是姓名，而不是编号。

需要注意的是，做左外连接时，应根据所要求查询的信息来安排两个表在"LEFT OUTER JOIN"两边的位置。


                                                                   图 (五)

等价于下图的查询方式，注意(+)的位置：


                                                               图 (六)

图五、图六解决的问题是，把所有员工及其老板的名字(如果有的话)显示出来，包括没有老板编号，或编号不在员工编号范围的员工记录，我们现在关心的是BOSS值为空的员工。图一告诉我们，员工总人数是14个，但是这14位员工并不都有属下。下面你可能会要求我把所有的老板列出来，不管他有没有属下，如果有就列出来，没有就空着。这么做似乎毫无意义，实际上是换角度看问题，此时我们关注的是ENAME为空的BOSS。右外连接能帮我们解决这个问题。以下是右外连接RIGHT OUTER JOIN及其等价的查询结果，记录条数为20条。需要注意的是做连接的表的位置关系。


                                                                 图 (七)


                                                               图 (八)

最后一个FULL OUTER JOIN就是把LEFT OUTER JOIN与RIGHT OUTER JOIN结合起来，查询来自做连接的两表中满足匹配条件和不满足匹配条件的记录，获得记录条数相对而言也是最多的。查询结果如下图所示：


                                                                图 (九)

 

实例（二）

A表和B表的id列含义相同。现要求对比A、B表中id相同的记录的colC和colD字段的值的变化情况，以及A、B表中id不同的记录的colC和colD字段的值。

将A、 B表中的记录导入数据库Microsoft Access，写一个全外连接语句

select A.id, B.id, A.colC, B.colC, A.colD, B.colD from A left join B on A.id=B.id
UNION select A.id, B.id, A.colC, B.colC, A.colD, B.colD from A right join B on A.id=B.id;