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?

1 Answer

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.

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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 9, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...