IN vs. EXISTS

This article explains the complete overview of IN and EXISTS clause. It is one of the most common questions asked by developers who write SQL queries to filter for specific values. The main difference between them is that IN selects a list of matching values, whereas EXISTS returns the Boolean value TRUE or FALSE. Before making the comparison, we will first know these SQL clauses.

IN Operator

The IN operator is used to retrieves results when the specified value matches any value in a set of values or is returned by a subquery. This operator allows us to specify multiple values along with the WHERE clause. It reduces the use of multiple OR conditions in SELECT, INSERT, UPDATE, and DELETE queries; that's why it is also called the shorthand for multiple OR conditions.

In this operator, the inner query executes first, and the result obtained is used by the outer query to display the output. It should be remembered that the inner query is executed only once. The IN operator has the following syntax:

SELECT column_name(s) 
FROM table_name 
WHERE column_name IN (value1, value2, - - - - );

Let us take an example to understand this operator. Suppose we have a table named customer that contains the following data:

If we want to get all customer details whose occupation is either doctor, engineer, or scientist, then we can use the statement as follows:

mysql> SELECT * FROM customer  
WHERE occupation IN ('Doctor', 'Scientist', 'Engineer');  

Here is the output:

EXISTS Operator

EXISTS is a Boolean operator which checks the subquery result and returns an either TRUE or FALSE value. It is used in combination with subquery and checks whether a row is returned through this subquery or not. This operator returns TRUE if the subquery returns single or multiple records. Otherwise, it gives a FALSE result when no records are returned.

When the EXISTS operator detects the first true event, it automatically terminates for further processing. This feature enhances the query's efficiency. We can use the EXISTS operator with SELECT, UPDATE, DELETE, and INSERT statements. The following is the syntax of EXISTS operator:

SELECT col_names  
FROM tab_name  
WHERE [NOT] EXISTS (  
    SELECT col_names   
    FROM tab_name   
    WHERE condition  
);  

Let us take an example to understand this operator. Suppose we have a table named customer and order containing the following data:

If we want to get all customer names and occupation who has placed at least one order, then we can use the statement as follows:

mysql> SELECT name, occupation FROM customer  
WHERE EXISTS (SELECT * FROM Orders   
WHERE customer.cust_id = Orders.cust_id);

Here is the output:

Key differences between IN and EXISTS Operator

The following points explain the main differences between IN and EXISTS clause:

The IN clause scan all records fetched from the given subquery column, whereas EXISTS clause evaluates true or false, and the SQL engine quits the scanning process as soon as it found a match.
When the subquery results are large, EXISTS operator provides better performance. In contrast, when the sub-query results are small, the IN operator is faster than EXISTS.
IN operator always picks the matching values list, whereas EXISTS returns the Boolean values TRUE or FALSE.
EXISTS operator can only be used with subqueries, whereas we can use the IN operator on subqueries and values both.
The EXISTS clause can compare everything with NULLs, while the IN clause can't compare anything with NULL.
IN operator performs a direct match between the columns specified before the IN keyword and a subquery result. Conversely, EXISTS operator does not check for a match because it only verifies data existence in a subquery.

IN vs. EXISTS Comparison Chart

The following comparison chart explains their main differences in a quick manner:

SN	IN Operator	EXISTS Operator
1.	It is used to minimize the multiple OR conditions.	It is used to check the existence of data in a subquery. In other words, it determines whether the value will be returned or not.
2.	It compares the values between subquery (child query) and parent query.	It does not compare the values between subquery and parent query.
3.	It scans all values inside the IN block.	It stops for further execution once the single positive condition is met.
4.	It can return TRUE, FALSE, or NULL. Hence, we can use it to compare NULL values.	It returns either TRUE or FALSE. Hence, we cannot use it to compare NULL values.
5.	We can use it on subqueries as well as with values.	We can use it only on subqueries.
6.	It executes faster when the subquery result is less.	It executes faster when the subquery result is large. It is more efficient than IN because it processes Boolean values rather than values itself.
7.	Syntax to use IN clause: SELECT col_names FROM tab_name WHERE col_name IN (subquery);	Syntax to use EXISTS clause: SELECT col_names FROM tab_name WHERE [NOT] EXISTS (subquery);

Conclusion

In this article, we have made a comparison between IN and EXISTS operators. Here, we conclude that both clauses work for the same purpose, but their internal working is different. In other words, they differ in their logical working. We can select any of them according to our requirement, but if we have a table that contains several records (large data), it is better to use EXISTS rather than IN operator.

Next TopicGROUP BY vs. ORDER BY

← prev next →