Using “EXISTS” makes query faster then using “IN”

There is an alternative way to substitute the using of “In” when querying SQL, which will make the process is faster and more effiecient.
Suppose you are going to get the information of all products which are included in promotion program:
“Select Product_ID, Produc_Description from Products Where Product_ID in (select distinct Product_ID from Promotions)”

The query will display all products from table Products which records are exist in table Promotions.
Now, try to replace “in” with “Exists”:
“Select P.Product_ID, P.Product_Description from Products P where EXISTS (select * from Promotions R where R.Product_ID=P.Product_ID)”

This last query will give the same result as the first one.
But if you work with huge database, the using of “Exist” will be more efficient compare to “In”, because using “in”, the subquery will read all data in table Promotions, then filter them uniquely using “Distinct”. After that, SQL Server will create a temporary table at tempdb for the subquery result, before they are used by main query.

The creation of temporary table, and storing subquery result into it, will make the process of main query suspended. And also, temporary table won’t have an index on it.

Using “Exist” for subquerying will be more efficient, because the subquery will be treated as a part of the main query … and thus, the result will be displayed faster..

Wanna try?