Performance exists vs in

Which one is better or worst?

Select * from table1 where col1 in ( subselect ) -- in

or

Select * from tabl1 where exists ( subselect ) -- exists

cheers,

There is no difference performance wise. SQL Server (from 2000 onwards) will mostly generate the same execution plan. You can observe this by comparing queries like above. However, you should watch out for the NOT IN vs NOT EXISTS type of queries. In case of NULL values in the column you are comparing using NOT IN, you will get wrong results. So it is best to use EXISTS for checking for presence of rows and NOT EXISTS for the opposite. This way there will be no confusion as to what you should use when and you will always get the best results & performance. Use IN clause when checking against a list of elements for example.

Script below shows the problem with NOT IN vs NOT EXISTS. The LEFT JOIN approach is shown for illustration only. Please do not use that technique since it is slower than NOT EXISTS due to additional operators.

create table #main_tbl(i int)
create table #in_tbl(i int)

insert #main_tbl values(1)
insert #main_tbl values(NULL)

insert #in_tbl values(2)
insert #in_tbl values(NULL)

select *from #in_tbl
select *from #main_tbl

go
Print 'Results with NULL in the main table & in_tbl...'
Print ''
select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)

select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )

select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go
Print 'Results with NULL in the in_tbl only...'
Print ''
update #main_tbl set i = 2 where i is null

select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)

select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )

select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go
Print 'Results with NULL in the main table only...'
Print ''
update #main_tbl set i = null where i = 2
update #in_tbl set i = 3 where i is null

select * from #main_tbl m where m.i not in(select i.i from #in_tbl i)

select * from #main_tbl m where not exists(select i.i from #in_tbl i where i.i = m.i )

select m.i from #main_tbl m left join #in_tbl i
on i.i = m.i
where i.i is null
go

drop table #main_tbl, #in_tbl
go