Difference between IN and EXISTS

Question

What is the difference between IN and EXISTS? Performance wise which one is better and how?

Vinay Singh · Answer

Hi The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed. IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once. EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times. The other difference is in performance( depending on which table is selected in outer/ inner query). EXISTS works faster than IN. you can check the performance plans of the above query for more info.

Nitin Sontakke · Answer

Well, I have a completely different view here. I am not even sure if you can even compare IN clause with EXISTS clause. I think it is like comparing oranges with apples. The IN clause looks like: WHERE Id IN (5, 7, 9, 10, 56, 90) This is then expanded by engine as WHERE Id = 5 OR Id = 7, etc. The EXISTS clause looks like: IF EXISTS(Select 1 from ..... where....) drop table.... Depending on a row is returned from the EXISTS query drop table will be executed. It is now conditional.There is one condition to evaluate. Please educate me if there is any other usage scenario of EXISTS.

Vinay Singh · Answer

Hi This answer is based on if we are using subquery. So As said in case of IN inner query first executed ,means first it takes out all the record and then start validation. so if there is large no of record it will take time to execute But in case of EXISTS it takes the first record and check with inner query that it satisfy the condation or not. it returns only true or false .if true the record will be shown else not for detail check the link http://www.w3resource.com/sql/special-operators/sql_exists.php

Saurabh · Answer

Thanks Vinay. But I have one doubt, as you said in case of EXISTS the inner query executes number of times (depending upon outer query rows). So how it will be faster than IN.