IN Vs EXISTS

Introduction

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

SELECT *

FROM tbl_Customers

WHERE Exists (SELECT *

              FROM tbl_Orders

              WHERE Orders.CustomerID = Customers.ID)

Syntax of IN

SELECT *

FROM tbl_Customers

WHERE ID IN (SELECT CustomerID

             FROM tbl_Orders)

We can Use Join

SELECT Customers.*

FROM Customers

    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