Difference between EXISTS and IN in SQL?

Question

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

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

Answer 1

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 ten rows can result from an outer query, then the inner query is executed that many numbers of times.

Syntax:

Where EXISTS (Sub-Query)

Example:

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.

Syntax:

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

Example:

SELECT emp.*

FROM employees emp

WHERE emp.dept_id IN (20, 30);

Differences between EXISTS and IN are as follows:

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

Answer 2

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: https://www.sqlinterviewsquestions.com/2021/01/best-explained-uses-of-exists-and-in.html

Answer 3

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.