Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

What is the difference between the EXISTS and IN clause in SQL?

When should we use EXISTS, and when should we use IN?

3 Answers

0 votes
by (40.4k points)

EXISTS: In this, 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. In EXISTS, if there are ten rows that can result from outer query, then the inner query is executed that many numbers of times.


Where EXISTS (Sub-Query)


SELECT emp.*

FROM employees e

WHERE EXISTS (SELECT 1 FROM departments dep WHERE dep.dept_id = 10);

IN: In this, 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 only once.


WHERE expression IN (Value 1, Value 2,…Value N)


SELECT emp.*

FROM employees emp

WHERE emp.dept_id IN (20, 30);

Differences between EXISTS and IN are as follows:



 EXISTS: This cannot compare the values between the sub-query query and parent query.

IN: It can compare the values between sub-query and parent queries.

 The output of EXISTS can be either FALSE or TRUE

The output of IN can be TRUE or NULL or FALSE

 EXISTS is used to determine if any values are returned or not.

Whereas, IN can be used as a multiple OR operator.

 If the sub-query result is large, then EXISTS is faster than IN.

If the sub-query result is less, then IN is faster than EXISTS

Once the single positive condition is met in the EXISTS condition then the SQL Engine will stop the process.

In the IN-condition SQL Engine compares all the values

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL certification.

0 votes

There is a primary difference between IN and EXISTS

  • In IN clause, first sub query runs and the output is used as input for outer query. 
  • In Exists clause, first the main query runs and take one row and check it in the sub query as input for the sub query in where clause. 

Now in Exists clause we are feeding sub query one input at a time and in IN clause we are feeding main query one or more than one input depending on sub query's output. So Exists clause is faster. 

One point you need to remember here, if data set is less then always use IN clause because it will check all at once where EXISTS clause will check one by one. 

But when Data set is bigger, use EXISTS clause always.

Source :

0 votes
by (140 points)

EXISTS is used to determine if any values are returned or not. Whereas, IN can be used as a multiple OR operator. If the sub-query result is large, then EXISTS is faster than IN. Once the single positive condition is met in the EXISTS condition then the SQL Engine will stop the process.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers


94.1k users

Browse Categories