先定义两个表:
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中。