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 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.