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
You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL certification.
