Thursday 6 March 2014

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

1 comment:

  1. If you don't need to return any columns from another table, would you prefer exists over joining the tables?

    ReplyDelete