mysql关于自联结的问题

  在练习sql语句的自联结时，一不小心的一个失误，却让自己发现了一个自认为很细节的问题，当然很大原因是因为我是初学者吧。

       于是，我在CU论坛里发了帖子，一位网名叫cenalulu的DBA很耐心的讲解了我的问题，在这里谢谢他。

       我们讨论的帖子连接为： http://bbs.chinaunix.net/thread-3770316-1-1.html

        在这里，我将这个问题整理出来，希望大家以后遇到此类问题能有所帮助。

        这里我先将我用到的表的列打印出来：

        然后将其内容也打印出来：

现在，我们发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。我们使用自联结来解决这个问题：

我们执行如下语句：

select p1.prod_id, p1.prod_name from products as p1, products as p2  where p1.vend_id = p2.vend_id and  p1.prod_id = 'dtntr' ;

运行结果：

我是不小心将p2.prod_id = 'dtntr'写为p1.prod_prod_id的，后来发现与书上的结果完全不一样。后来才发现这个地方的失误。

按照书上的程序，执行如下语句：

select p1.prod_id, p1.prod_name from products as p1, products as p2  where p1.vend_id = p2.vend_id and  p2.prod_id = 'dtntr' ;

运行结果：

上面的结果才是我们想要的。

我就很好奇，既然是自联结，说明两张表是一样的啊，为什么结果却截然不同呢？

那位热心的cenalulu回答说：

结果不一样的原因是P1表的 dtntr --- vend_id 的对应关系和 P2表的这两列对应关系不同。建议你把 p1.vendor_id,p1.prod_id,p1.prod_name,以及p2 的这三列都select出来，分别执行一次，你就知道原因了。

于是我执行了如下语句：

select  p1.vend_id as vend_id1, p1.prod_id as prod_id1, p1.prod_name as prod_name1,  p2.vend_id as vend_id2, p2.prod_id as prod_id2, p2.prod_name as prod_name2 from products as p1, products as p2  where p1.vend_id = p2.vend_id and  p2.prod_id = 'dtntr';

结果如下：

经过这么一看，规律我是找到了。select从p1表中选取，过滤条件是p2表中的内容（p1与p2是通一张表）。

要么，select从p2表中选取，过滤条件是p1表中的内容。

总之，如果是自联结的话，select的与后面过滤的表必须是两个别名不同的表。如果相同，就不会得出我们希望的结果。

虽然规律貌似是这么找出来了，不过，我真心地不懂是为什么。于是，又请教了一下，他告诉我说：

实际上就是差别就在于是用驱动表做约束，还是用数据表做约束。

当时，我看了这个回答并不太明白，因为我这个菜鸟还不懂什么叫做驱动表。

不过，好像大概就是参照物的那个意思，我就上网搜了一下，下面解释一下驱动表：

通俗地说，就是先从哪个表开始搜索，找到好的驱动表语句，优化就成功了一半。

网上给了个例子：

eg: select * from a,b where a.id = b.id and a.姓名 = '美格瑞恩' and b.性别 = '女';

在a,b表同等数量级的情况下，显然用a表做为驱动表比较好。因为姓名相对于性别来说，可以过滤掉更多的数据,所以想办法使你的执行计划扫描a表先，再通过nest loop与b表关连比较理想。

回到我们的问题，再看看如下语句：

select  p1.vend_id as vend_id1, p1.prod_id as prod_id1, p1.prod_name as prod_name1,  p2.vend_id as vend_id2, p2.prod_id as prod_id2, p2.prod_name as prod_name2 from products as p1, products as p2  where p1.vend_id = p2.vend_id and  p2.prod_id = 'dtntr';

这里，我们使用了p2表作为驱动表，此时的p2表已经是过滤以后的表了，不像最早的那张原始表。

那么select的时候，如果还用p2表作为数据表的话，筛选出来的结果一定是错的。因为我们筛选出来的不是原数据表中的内容，而是从过滤后的表中进行select的。

所以，我们使用不一样的自联结的表的别名，就是为了保存原始的数据表，又能产生新的我们需要的、过滤后的驱动表。