create table tt(
id int identity(1,1),
name varchar(10)
)
insert tt
select 'a' union all
select 'b' union all
select 'c'
create table tt1(
id int identity(1,1),
name varchar(10)
)
insert tt1
select 'a' union all
select 'b' union all
select 'c'
--结果:
select * from tt left join tt1 on tt.id=tt1.id --提取id相等的
id name id name
--------------------------
1 a 1 a
2 b 2 b
3 c 3 c
--结果1:
select * from tt left join tt1 on tt.id=tt1.id
where tt.id=1 --用的是where,可以看出用where 和 and 的区别了:)
id name id name
--------------------------
1 a 1 a
--结果2:left join 就是查询tt表中所有记录,如果有返回对应值,没有为NULL
select * from tt left join tt1 on tt.id=tt1.id
and tt.id=1 --用的是and,且是主表tt.id=1
id name id name
--------------------------
1 a 1 a
2 b NULL NULL
3 c NULL NULL
--结果3:这个返回跟上面一样的结果
select * from tt left join tt1 on tt.id=tt1.id
and tt1.id=1 --用的是and,且是从表tt1.id=1
id name id name
--------------------------
1 a 1 a
2 b NULL NULL
3 c NULL NULL
--结果4:
select * from tt left join tt1 on tt.id=1 --关键是去掉了id相等这个条件
id name id name
--------------------------
1 a 1 a
1 a 2 b
1 a 3 c
2 b NULL NULL
3 c NULL NULL
--对结果4,很多人不了解,看zjcxc(邹建) 的解释
left join 嘛, 无非是左边表为基础, 扫描右边表匹配的记录
先是左边表的第1条记录
1 a
按条件 a.id=1, 来扫描右边表的记录
对于右边表的每条记录, 显然 a.id=1 这个条件都是成立的, 所以第1条记录匹配后的结果是:
1 a 1 a
1 a 2 b
1 a 3 c
---------------------------------------------
然后再扫描第2条记录
2 b
对于条件 a.id=1, 在边表中没有与之匹配的记录, 所以右边表为NULL
因此第2条记录匹配的结果为
2 b NULL NULL
----------------------------------------------
第3条记录与第2条记录一样, 匹配的结果是
3 c NULL NULL
---------------------------------------
因此最终结果是5条记录
1 a 1 a
1 a 2 b
1 a 3 c
2 b null null
3 c null null
---再看:
select * from tt left join tt1 on tt1.id=1 --是从表tt1.id=1
id name id name
--------------------------
1 a 1 a
2 b 1 a
3 c 1 a
--解释:上面的结果是这样取的,先取主表1 a,联立从表tt2,正好
其第一条数据id=1,满足条件,所以输出:
1 a 1 a
然后再是从表第二条数据 2 b,id!=1,出输出,同理3 c也不满足条件,
再主表 2 b,联立从表第一条数据1 a,满足条件,输出,
以后依次类推...............
--最主要的就是在连接里的 and 和 where 是有本质区别。
1.
select * from a left join b on a.id=b.id where a.id=1
2.
select * from a left join b on a.id=b.id and a.id=1
where里的条件是对连接后的结果集进行筛选,而on里的条件是关联时进行比较用的
--再看一下更加详细的解释:
1、首先列出笛卡尔乘积
1 a 1 a
1 a 2 b
1 a 3 c
2 b 1 a
2 b 2 b
2 b 3 c
3 c 1 a
3 c 2 b
3 c 3 c
2、对每个左边的纪录,看有没有符合on条件的,如果有,保留符合条件的,去掉不符合条件的,如果没有,
保留左边和右边是null的一条纪录
1 a 1 a
1 a 2 b
1 a 3 c --左边1的都符合都保留
2 b null null --左边2的都不符合,变成一条右边null的纪录
3 c null null --左边3的都不符合,变成一条右边null的纪录
3、再去掉where条件不符合的,就是结果(这里的例子没有where条件,所以结果不变,大家自己可以加条件测试下)
1 a 1 a
1 a 2 b
1 a 3 c
2 b null null
3 c null null
对于left join的on条件:
用左边的记录去匹配右边的每条记录,匹配条件就是on里的条件,若右边有满足条件的记录,则取出来,
若没有匹配的记录,则给个null值,对于其后的where条件,
是对整个left join后产生的结果集进行筛选,所以条件放在on里和where里是完全不同的意思,切记!!!