Understanding the Distinction Between EXISTS and IN in SQL

1. Overview

When working with data in SQL (Structured Query Language), we often need to find specific information or check for certain conditions. To perform these tasks, we normally use two helpful operators: EXISTS and IN. Although they appear to be similar, they function differently.

We use the IN operator to filter rows based on a specific list of values. On the other hand, we use the EXISTS operator to look for rows that match certain criteria in a subquery.

In this tutorial, we’ll explore the IN and EXISTS operators in SQL and determine their differences. We’ve used the Baeldung University database schema and tested our examples on MS SQL Server 2022, PostgreSQL 16.3, and MySQL 8 databases.

2. IN Operator

The IN operator lets us find results when a value matches any values in a given list or is returned by a subquery. Moreover, we can use the IN operator instead of multiple OR conditions.

Furthermore, we can provide values directly to the IN operator or use a query result as the input. In the subquery case, the IN operator first runs the subquery and produces a list of values. After that, the outer query uses a return list to filter its results. We should note that the inner query is only executed once, and its results are reused by the outer query.

2.1. Syntax

Let’s check the syntax of the IN operator:

SELECT column_name 
FROM table_name 
WHERE column_name IN (1st_value, 2nd_vaule, ...);

Here, column_name is a column that’s matched against each value in the list. Moreover, if a match occurs, the IN operator evaluates it as TRUE; if no match occurs, the IN operator evaluates it as FALSE.

2.2. Example Query

Let’s consider a table Department from our University Database. For example, if we want to see the details of the department whose specific code can be ME or CE, we can use the IN operator with an input set of values:

SELECT * 
FROM Department 
WHERE code IN('ME', 'CE');

Here, we use SELECT to select data from all columns of the Department table and filter the result based on the specified condition. After executing, it will return all rows from the Department table where the code is either ME or CE.

Let’s view the result of the above query in PostgreSQL:

Alternatively, we can use a subquery with the IN operator. For example, let’s filter all Exam table column data with an id that’s also present in the Department table:

SELECT * 
FROM Exam 
WHERE id IN (SELECT id FROM Department);

Here, the (SELECT id FROM Department) is a subquery — a query inside another query. Moreover, it selects the id column from the Department table and outputs it as a list. So, when we put it all together, this complete query selects all columns from the Exam table where its id matches one in the list of id values from the Department table.

Let’s view the output of the above query:

We can also achieve the above results with the EXISTS operator.

3. EXISTS Operator

The EXISTS operator lets us see if a subquery has any records. Moreover, EXISTS outputs TRUE if there’s at least one record, and FALSE if there are no records. We often use the EXISTS operator with subqueries to verify if a record is available in another table.

Unlike the IN operator, EXISTS efficiently stops scanning when it finds the first matching record. This makes EXISTS particularly useful in conditional statements, where it can significantly improve performance and speed up queries.

3.1. Syntax

Let’s view the syntax of EXISTS:

SELECT column_name 
FROM table_name 
WHERE EXISTS (subquery);

Here, the subquery is executed for every row in the outer query’s table, allowing us to check for the existence of related records in another table. Furthermore, if the outer query returns multiple rows, the inner query is executed numerous times, once for each row, which can impact performance.

3.2. Example Query

This example is similar to the IN subquery example, where we return all rows from the Exam table with matching ids in the Department table. We can now use the EXISTS operator to do the same thing:

SELECT * 
FROM Exam 
WHERE EXISTS(SELECT * FROM Department WHERE Exam.id=Department.id);

In the subquery, we check if an exam’s id matches a department’s id. If there’s a match (meaning one row is returned), the subquery is considered TRUE. If TRUE, then this allows the outer query, which selects all exam data (SELECT * FROM Exam), to proceed for that specific student’s id.

4. IN vs. EXISTS Operators

Let’s explore the key distinctions between the IN and EXISTS operators:

We can choose either one, depending on what suits our particular needs. However, when dealing with large datasets, the EXISTS operator is generally more efficient than the IN operator.

5. Conclusion

In this article, we explored the similarities and differences between the IN and EXISTS operators in SQL. The IN operator allows us to verify whether a specific value is present within a defined set. Furthermore, the EXISTS operator evaluates whether a subquery returns TRUE or FALSE.

Moreover, we can use the IN operator when executing a short list of values or when a subquery returns only a few rows. On the other hand, we can use the EXISTS operator to check if the value exists in another table or check against multiple columns.

Furthermore, the scripts used in the article are available over on GitHub.