Difference Between 'IN' and 'EXIST' Operator

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 TABLE_2
    WHERE TABLE_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 TABLE_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.