记一次SQL外连接and条件的误用

前段时间，在查找项目的一个BUG时发现该问题，为此特意整理该篇，以加深自己的印象，并给相关朋友一个注意和参考。

 

 

下面以一个小实例来讲该问题：

 

1、先建两张表并插入几条简单的测试数据，如下图

 

2、我们先看看左连接的结果（右连接类似，不再举例）

SELECT * 
FROM Table1 left join Table2
on Table1.cc = Table2.cc

 

这个结果，没什么好多说的，不管连接字段是否为null，table1中所有数据行全部包含。

 

3、我们再在左连接的基础上，再用and接个条件看看

SELECT * 
FROM Table1 left join Table2
on Table1.cc = Table2.cc
and Table1.cc = 'c2'

 

 

看到这个结果，有些朋友就会有些意外了，本人要说的这个BUG正是这个and条件误用导致。

本来期望的结果是只有第二条结果，可是却出现了3条。

查找了下相关资料：当把条件加入到 join子句时，SQL Server、Informix会返回外连接表的全部行，然后使用指定的条件返回第二个表的行。也就是在原来数据行不变的情况下，and条件是对第二个表的数据行进行了筛选。

 

4、最后我们看看在左连接的基础上，用where接条件的情况

 

SELECT * 
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的结果：