IN and EXISTS operators in SQL

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

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

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 sub-query to join the tables. Not doing so will result in fetching all the records from the main table. for example 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.

What is the main difference between the IN and EXISTS clause in sub-queries??

The main difference between the IN and EXISTS predicate in sub-query is the way in which the query gets executed.

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.