left join和inner join

根据自己的实际情况，如果关联字段在一边数据量多一边少建议使用left join，否则inner join

---

create table #t1(userid int not null)
create table #t2(userid int not null)
declare @i int
set @i = 1
while @i < 30000
begin
insert into #t1(userid) values(@i)
set @i= @i + 1
end
set @i = 29000
while @i < 40000
begin
insert into #t2(userid) values(@i)
set @i= @i + 1
end

declare @time datetime,@show varchar(8000)
set @time = getdate()

select *
from #t1 a inner join #t2 b
on a.userid=b.userid

set @show = ‘inner join:’ + cast(datediff(ms,@time,getdate()) as varchar)
set @time = getdate()
print @show

select *
from #t1 a left join #t2 b
on a.userid=b.userid

set @show = ‘left join:’ + cast(datediff(ms,@time,getdate()) as varchar)
set @time = getdate()
print @show

---

(1000 row(s) affected)
inner join:173

(29999 row(s) affected)
left join:656

————————————————————————————

drop table #t1,#t2
create table #t1(userid int not null)
create table #t2(userid int not null)
declare @i int
set @i = 1
while @i < 10000
begin
insert into #t1(userid) values(@i)
set @i= @i + 1
end
set @i = 9000
while @i < 40000
begin
insert into #t2(userid) values(@i)
set @i= @i + 1
end

declare @time datetime,@show varchar(8000)
set @time = getdate()

select *
from #t1 a inner join #t2 b
on a.userid=b.userid

set @show = ‘inner join:’ + cast(datediff(ms,@time,getdate()) as varchar)
set @time = getdate()
print @show

select *
from #t1 a left join #t2 b
on a.userid=b.userid

set @show = ‘left join:’ + cast(datediff(ms,@time,getdate()) as varchar)
set @time = getdate()
print @show

---

(1000 row(s) affected)
inner join:360

(9999 row(s) affected)
left join:326