请问left outer join和no in的不同
请教left outer join和no in的不同
not In
left outer join
两句的效果是一样的,效率上来说哪个比较好..手上没有大数据库进行测试,有没人帮忙看看
------解决方案--------------------
not In
- SQL code
select * from table where id not in (select top 100 id from table order by id desc)
left outer join
- SQL code
select * from table left outer join (select top 100 id as tId from table order by id desc) t on t.tId = table.id where t.tId is null
两句的效果是一样的,效率上来说哪个比较好..手上没有大数据库进行测试,有没人帮忙看看
------解决方案--------------------
- SQL code
go if OBJECT_ID('tbl')is not null drop table tbl go create table tbl( id int identity(1,1), QQ VARCHAR(10) ) go declare @a int set @a=1 while @a<=1000000 begin insert tbl select ltrim(cast(RAND()*1000000000 as int)) set @a=@a+1 end select * from tbl where id not in (select top 100 id from tbl order by id desc) --110万条数据不加任何索引第一次30s,第二次24s select * from tbl left join (select top 100 id as tId from tbl order by id desc) t on t.tId = tbl.id where t.tId is null --110万条数据不加任何索引第一次24s,第二次24s