http://www.cnblogs.com/fengyao/archive/2010/05/03/1726364.html
前段时间,在查找项目的一个BUG时发现该问题,为此特意整理该篇,以加深自己的印象,并给相关朋友一个注意和参考。
下面以一个小实例来讲该问题:
1、先建两张表并插入几条简单的测试数据,如下图
2、我们先看看左连接的结果(右连接类似,不再举例)
FROM Table1 left join Table2
on Table1.cc = Table2.cc
这个结果,没什么好多说的,不管连接字段是否为null,table1中所有数据行全部包含。
3、我们再在左连接的基础上,再用and接个条件看看
FROM Table1 left join Table2
on Table1.cc = Table2.cc
and Table1.cc = ' c2 '
看到这个结果,有些朋友就会有些意外了,本人要说的这个BUG正是这个and条件误用导致。
本来期望的结果是只有第二条结果,可是却出现了3条。
查找了下相关资料:当把条件加入到 join子句时,SQL Server、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。也就是在原来数据行不变的情况下,and条件是对第二个表的数据行进行了筛选。
4、最后我们看看在左连接的基础上,用where接条件的情况
FROM Table1 left join Table2
on Table1.cc = Table2.cc
where Table1.cc = ' c2 '
O(∩_∩)O哈!这个结果正是本来所期望的结果。因为如果将条件放到where子句 中,SQL Server将会首先进行连接操作,然后使用where子句对连接后的行进行筛选。
补充:
上面的例子对于and的用法展示的还不是很清晰,为了让大家更深刻的理解“当把条件加入到 join子句时,SQL Server、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。也就是在原来数据行不变的情况下,and条件是对第二个表的数据行进行了筛选”这句话,再举个小例子说明。
(对于楼下的一些朋友给的一些另外的方法,我非常感谢,但是我想声明的是该篇是讲外连接and的用法,标题不就这样注明的吗。我也不会无缘无故非要用个左连接,期望结果是举例的一部分,是为了方便说明。)
我们在原来的表上加入新的数据,如下图:
看看这时左连接的结果:
再看看加and的结果: