sql server中on和where的区别

区别：
1）on是生成临时表时使用的条件，即不满足条件的不会放入临时表；
2）where是生成临时表后使用的条件，即将临时表里不满足条件的过滤掉，再生成最终的结果；

对于left join和right join和full join来讲，on和where的最终结果可能是不同的

比如

• select * from a left join b on xxxxx, 返回结果记录数必然大于等于a表的记录数；
• 而 select * from a left join b where 1 = 0, 则返回结果记录数必然为0

对于join|inner join来讲，on和where的结果一样，但预计on可以更快的过滤掉条件，是不是可以更快一些呢？

create table op_test1(
    id bigint IDENTITY(1,1) NOT NULL,
	name varchar(64)
)
create table op_test2(
    id bigint IDENTITY(1,1) NOT NULL,
	name varchar(64)
)
declare @id int
set @id = 1
while @id < 100000
begin
	insert into op_test1(name) values(CAST(@id as varchar(64)))
	insert into op_test2(name) values(CAST(@id as varchar(64)))
	set @id = @id + 1
end

下面进行2条sql的试验，即条件b.name = '1000’分别在on和where上

select a.*, b.* from op_test1 a inner join op_test2 b on a.id = b.id where b.name = '1000'
select a.*, b.* from op_test1 a inner join op_test2 b on a.id = b.id and b.name = '1000'

速度都非常快，看不出差别，看下它们的执行计划

可以看到生成临时表时，op_test2表里不是全量查询，即试验中2条sql的执行计划一样的，条件写在on和where的执行过程是完全一致的！！！