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

“IN”, “EXISTS” clause and their performance

Introduction

To improve the performance of the Query, the general guideline is not to prefer the "IN" Clause. The guideline of the MS SQL query performance says that if we needs "IN" clause, instead of using "IN" clause we must use the "EXISTS" clause.   As the "EXISTS" clause improve the performance of the query.

This article is related to "IN", "EXISTS" clause and their performance factors.

Is "IN" and "EXISTS" clause are same

IN Clause

Returns true if a specified value matches any value in a sub query or a list.

EXISTS Clause

Returns true if a sub query contains any rows.

So we see that the "IN" and the "EXISTS" cluse are not same. To support the above definition lets takes an example.

-- Base Tabe

IF OBJECT_ID('emp_DtlTbl') IS NOT NULL

   BEGIN

     DROP TABLE emp_DtlTbl;

   END

GO

IF OBJECT_ID('emp_GradeTbl') IS NOT NULL

   BEGIN

     DROP TABLE emp_GradeTbl;

   END

GO     

CREATE TABLE emp_DtlTbl

       (EMPID   INT        NOT NULL IDENTITY PRIMARY KEY, 

        EMPNAME VARCHAR(50)NOT NULL);

GO

CREATE TABLE emp_GradeTbl

       (EMPID   INT        NOT NULL IDENTITY PRIMARY KEY, 

        GRADE   VARCHAR(1) NOT NULL);

GO

-- Insert Records

INSERT INTO  emp_DtlTbl

       (EMPNAME)

VALUES ('Joydeep Das'), ('Sukamal Jana'), ('Sudip Das');

GO

INSERT INTO  emp_GradeTbl

       (GRADE)

VALUES ('B'), ('B'), ('A');

GO

-- [ IN ] Clause Example-1

SELECT *

FROM   emp_DtlTbl;

WHERE  EMPID IN(SELECT EMPID FROM  emp_DtlTbl);           

-- [ IN ] Clause Example-2

SELECT *

FROM   emp_DtlTbl

WHERE  EMPID IN(1,2,3);

-- [ EXISTS ] Clause Example

SELECT a.*

FROM   emp_DtlTbl a

WHERE  EXISTS(SELECT b.*

              FROM   emp_DtlTbl b

              WHERE  b.EMPID = a.EMPID);

  

Performance Factors

To understand the performance factors let see the actual execution plan for "IN" and "EXISTS" clauses.

Take this example:

-- [ IN ] Clause Exampl

SELECT *

FROM   emp_DtlTbl

WHERE  EMPID =(SELECT EMPID

               FROM   emp_DtlTbl

               WHERE  EMPID = 2);           

-- [ EXISTS ] Clause Example

SELECT a.*

FROM   emp_DtlTbl a

WHERE  EXISTS(SELECT b.*

              FROM   emp_DtlTbl b

              WHERE  b.EMPID = 2

                     AND b.EMPID = a.EMPID);

If we compare the total query costs of the both MS SQL query, we see that the IN clause query cost is higher than the EXISTS clause query costs.

Special notes

Please note that: Here the data of the table is limited, so we cannot measure the performance factors.

Hope you like it.

Posted by: MR. JOYDEEP DAS