Home > Functions and DMVs, Joins, SQL Queries, SQL Server, T-SQL > Difference between IN and EXISTS operators in SQL

Difference between IN and EXISTS operators in SQL

This is a commaon question that comes to each developers mind.  Most of them considers IN and EXISTS as same but with different syntax. This aint true .

We can use both operators to fetch the same results. Let me take few examples before we go to the actuals.

SELECT * FROM TABLE_1 returns records as shown below

Field1
1
2
3
4

SELECT * FROM TABLE_2 returns

Field2 Field3
1 4
2 6
3 7
4 8

if we want to get the data in TABLE_1 which are present in “Field3” of TABLE_2 then we can use the query with IN operator as

SELECT * FROM TABLE_1
WHERE FIELD1 IN ( SELECT FIELD3 FROM TABLE_2)

We can also use the query with EXISTS as

SELECT * FROM TABLE_1
WHERE EXISTS(    SELECT ‘X’
FROM TAB
LE_2
WHERE TAB
LE_1.FIELD1 = TABLE_2.FIELD3
)

when using EXISTS always use the where clause in the subquery to join the tables. Not doing so will result in fetching all the records from the main table. for eample if we consider the query below

SELECT * FROM TABLE_1
WHERE EXISTS(    SELECT Field3
FROM TAB
LE_2

)

will fetch all the records from TABLE_1 and is same as the query

SELECT * FROM TABLE_1

The other difference is in performance( depending on which table is selected in outer/ inner query). EXISTS works faster than IN. you can check the performance plans of the above query for more info.


Advertisements
  1. misty
    July 22, 2012 at 12:34 am

    Thanks so much for the intuitive examples, which are hard to find on the net.

  2. December 27, 2013 at 4:34 am

    Please share and like new blog on Java – http://javacodeimpl.blogspot.com/

  3. haadiya
    January 18, 2015 at 1:17 pm

    you didnt explain the diiference.you mentioned only about the performance and the syntax

  4. haadiya
    January 18, 2015 at 1:19 pm

    your example does provide the difference only on the syntax

  5. haadiya
    January 18, 2015 at 1:41 pm

    according to your post both IN and EXISTS is same ,the difference is with the syntax and performance

  6. sarko
    February 4, 2015 at 7:27 pm

    Really helpful for me , thanks!

  7. rinkal
    August 12, 2015 at 7:39 am

    In and EXISTS both are same, but the difference is in syntax and performance

  1. August 28, 2016 at 5:27 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: