The EXISTS operator in SQL provides us with a convenient method for retrieving data based on whether other data exists (or does not exist). More specifically, it is a logical operator used to evaluate the result of a subquery and return a Boolean value indicating whether a row was returned. Although IN operators can be used for similar purposes, it is important to note some differences between them. Today's blog will introduce several examples of how to use the EXISTS operator and provide some guidance on when to use EXISTS instead of IN.

The Application of EXISTS in Practice

Although the EXISTS operator can be used in SELECT, UPDATE, INSERT, or DELETE statements, to maintain simplicity, we will focus on SELECT queries. Therefore, the syntax we will use will be very similar to the following form:

We will perform our queries on several tables in PostgreSQL, such as the customer and account tables, which are commonly found in bank databases. Here are the tables displayed in the Navicat for PostgreSQL grid view:

Now, we can use the following query to view all customers with accounts associated with their customer IDs:

The following is the result of executing the above query in the Navicat Premium query editor:

Using NOT EXISTS

On the contrary, adding the NOT keyword before the EXISTS operator will cause the query to only select records in subqueries that do not have matching rows. We can use NOT EXISTS to retrieve all isolated accounts, i.e. accounts without associated customers:

Since there is no customer with this ID in the customer table, it returned the account of customer # 4.

Replace Exits with Joins

Querying using the EXISTS operator may be a bit slow because subqueries need to be executed once for each row of the outer layer query. Therefore, you should consider using connections as much as possible. In fact, we can use LEFT JOIN to rewrite the EXISTS query above:

IN vs EXISTS operator

Although the IN operator is typically used to set a filter for a list of values in a column, it can also be applied to the results of subqueries. The following is the equivalent query of our first query, but this time we used IN instead of EXISTS:

Please note that we can only select the columns we want to compare, not SELECT *. However, an IN query will produce the same result:

Due to the similarity between these two operators, database developers are often unsure which one to use. Generally speaking, when you want to filter rows based on a specific value list, you should use the IN operator. When you want to check if there are rows in a subquery that meet certain conditions, you should use EXISTS.