EXISTS vs.IN

[cricketer1]

What is the difference between using IN and EXISTS in subqueries and which is preferred.

Thanks

 

[collierd]

in is used in simple subqueries (can be run stand alone)
exists is used in correlated subqueries (dependant upon parent). Existance of rows meeting a particular query

 

[cricketer1]

thanks, any idea which one is preferred...IN is faster or EXISTS?

 

[SQLSister]

I looked at the execution plan of two queries that were written with in and exists and the execution plan was exactly the same.

Personally, I avoid using subqueries, you generally get better performance out of doing a join instead. This is not always true, so you should probably test both the subquery and the join.

Questions about posting. See faq183-874