Every developer knows about IN and EXISTS clause. Depends on situation to situation we use them. Here in this article we are typing to make some differences between them
What to use IN or EXISTS
We recommended to use the EXISTS instead of IN as performance wise EXISTS gives us better output.
Syntax wise Differences
Syntax of EXISTS
WHERE Exists (SELECT *
WHERE Orders.CustomerID = Customers.ID)
Syntax of IN
WHERE ID IN (SELECT CustomerID
We can Use Join
INNER JOIN Orders ON Customers.ID = Orders.CustomerID
Here if we think about the performance factors in mind, we recommended the order of Using Join, Using EXISTS and Last Order is Using IN Clause.
Disadvantage of Using IN clause
As we all know that IN clause decrees the performance of query, beside this is another disadvantage we find in IN clause.
Generally we are writing IN clause in this fashion
WHERE id IN (1,2,3,4)
But there is a limitation of Number of item within IN Clause and that is 10000
WHERE id IN (1,2,3,4,.....10000)
We can use maximum of 10,000 items within IN clause.
Hope you like it.
Posted by: MR. JOYDEEP DAS