EXISTS vs IN — SQL

Understanding the difference

Photo by Emily Morter on Unsplash

For Data Scientists and Data Analysts, SQL is a must-know skill. Writing a correct and optimized SQL query is sometimes a work of art. There can be many ways to formulate a SQL query for a given problem but usually one way is better than the rest, given the constraints of the system and the problem at hand.

This article discusses the use of IN Clause and EXISTS clause, the differences between them and when to prefer one over the other.

Let us consider the following sample data set:

Customers

Orders

We have been asked the following two questions?

Q1. Give the names of customers who belong to either Gold or Silver category?

Q2. Give the names of the customers who have ordered between 19 Sep and 20-Sep 2021.

For answering the first question, we can use the “In” clause in the SQL query as follows:

select name from customers where category in (‘Gold’,’Silver’)

IN clause retrieves all records which match with the given set of values. It acts as multiple OR conditions. IN clause scans all rows fetched from the inner query.