非等值连接中，连接条件与where条件的区别



先定义两个表：
DECLARE @tmpTable TABLE  (
 id INT ,
 NAME VARCHAR(100),
 age INT  
)

DECLARE @tmpClass TABLE(
 id INT,
 man_id INT,
 class_name VARCHAR(100) 
)

向两个表中插入数据：

INSERT INTO @tmpTable
SELECT 1,'jim',2
UNION
SELECT 2,'tom',5
UNION
SELECT 3,'sham',6

INSERT INTO @tmpClass
SELECT 1,1,'class 1'
UNION
SELECT 2,2,'class 2'

使用在左外链接中增加条件

SELECT *
FROM @tmpTable a
LEFT JOIN @tmpClass b ON a.id=b.man_id

         AND b.class_name LIKE '%1'  --左外连接条件

结果集：

id NAME age id         man_id    class_name
1   jim      2     1          1              class 1
2  tom     5      NULL  NULL       NULL
3  sham  6      NULL  NULL       NULL

使用where条件
SELECT *
FROM @tmpTable a
 LEFT JOIN @tmpClass b ON a.id=b.man_id
WHERE b.class_name LIKE '%1'

结果集：

id NAME age id  man_id     class_name
1   jim       2    1   1                class 1

结论：

使用非等值连接中增加外链接表列条件会过滤最终结果集，但是不会影响到外链接的原有语义，即：如果外链接表中存在对应则显示对应的列值，否则显示为null。

所以，在外链接表中对某列增加了条件后，尽管可以把不满足条件的记录过滤掉，但是在所有外链接表记录指定的列都没有满足指定的条件的记录后，系统会返回一个空行记录回来。切记！！！！

而使用where条件将会最终过滤结果集

所以碰到这样问题，要问几个问题：

1、是否需要外链接？

2、如果要用外链接，那么外链接的语义是否都清楚?

 left [outer] join,以左表为主（左表的记录数可能会随右表关联情况增多，即右表存在多个对应记录），即使右表无对应记录，左表记录仍然存在；

right [outer] join,逻辑同左外连接，只不过是以右表为准；

full [outer] join 全连接,即左右表中的记录在结果集中都会存在，只不过，连接不上的，左连不上，左表放空，右连不上，右表放空。

[inner] join,等值连接，当没有连接条件时同cross join,有连接条件时，只有满足条件的左右表的记录都存在，这些记录才会出现在最终结果中。

cross join,对左右表做笛卡尔积运算

另外，值得注意的是sql 2005后，出现了outer apply和cross apply运算符，主要用于将一个表同一个表值函数连接使用，详细参照这里：http://blog.csdn.net/xiaojia_boke/article/details/48208451

3、连接条件是不是想影响最终结果集，如果是，直接把条件转移到where中。