SQL: Difference Between IN and EXISTS Operator
#GoLearningPoint
Difference Between IN and EXISTS Operator
Both IN and EXISTS operator check for records correlation between Main Query(Outer Query)
And Inner Query(Sub Query)
***IN Operator
##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.
##IN operator scan all the values inside the IN block.
##IN picks the list of matching values.
##In case of a big number of list from the select list IN operator should not be used.
##IN can be used on sub-queries as well as with values.
##IN is used as multiple OR operators.
***EXISTS Operator
##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 nos.
of times.
##Exist operator is a Boolean operator.so it works more efficient and faster than IN operator as it will be faster to
process Boolean value rather than processing values itself.
##EXISTS returns the Boolean values like true or false. Exists is faster than in.
##EXISTS quit after 1st occurrence.
##Whereas 'EXISTS' can only be used on sub-queries.
##The exists shall be used with the proper condition this will definitely make the query optimal query.
##Exists is used to check whether the sub-query returns any rows.
Summary:
###If the majority of the filtering criteria is in the subquery, use IN (IN for big outer query and small inner query).
###If the majority of the filtering criteria is in the main query, use EXISTS (EXISTS for small outer query and big inner query).
