Sep 26, 2024 by Robert Gravelle

The SQL EXISTS operator offers us an easy way to retrieve data based on the existence (or non-existence) of some other data. More specifically, it's a logical operator that evaluates the results of a subquery and returns a boolean value indicating whether rows were returned or not. While the IN operator can be utilized for much the same purpose, there are some differences to be aware of. Today's blog will cover how to use the EXISTS operator using a few examples as well as provide some guidance as to when to use EXISTS rather than IN.

EXISTS In Action

Although the EXISTS operator can be used in a SELECT, UPDATE, INSERT or DELETE statement, we'll stick with SELECT queries to keep things simple. As such, the syntax we will be using will closely resemble this:

SELECT column_name(s) 
FROM table_name
WHERE EXISTS ( SELECT column_name(s) 
               FROM table_name
               WHERE condition );

We'll be executing our queries against a couple of PostgreSQL tables - customer and account - such as those we might find in a banking database. Here they are in Navicat for PostgreSQL's Grid View:

Now we can see all the customers who have an account associated with their customer_id using the following query:

SELECT *
FROM customer C
WHERE EXISTS ( SELECT *
               FROM account A
               WHERE C.customer_id = A.customer_id );

Here is the above query with the results in the Navicat Premium's Query Editor:

Using NOT with EXISTS

Conversely, prefacing the EXISTS operator with the NOT keyword causes the query to only select records where there is no matching row in the subquery. We can use NOT EXISTS to fetch all orphaned accounts, that is to say, accounts with no associated customer:

SELECT *
FROM account A
WHERE NOT EXISTS ( SELECT *
                   FROM customer C
                   WHERE A.customer_id = C.customer_id );

That returns the account for customer #4 since there is no customer with that ID in the customer table.

Replacing EXISTS with Joins

Queries that use the EXISTS operator can be a little slow to execute because the subquery needs to be executed for each row of the outer Query. For that reason, you should consider using joins whenever possible. In fact, we can rewrite the above EXISTS query using a LEFT JOIN:

SELECT C.*
FROM customer C
  LEFT JOIN account A ON C.customer_id = A.customer_id;

IN vs EXISTS Operators

Although the IN operator is typically used to filter a column for a certain list of values, it can also be applied to the results of a subquery. Here's the equivalent to our first query, this time using IN rather than EXISTS:

SELECT * 
FROM customer 
WHERE customer_id IN (SELECT customer_id FROM account);

Note that we can only select the column that we want to compare against, as opposed to SELECT *. Nonetheless, the IN query produces the same results:

With both operators being so similar, database developers are often unsure as to which to use. As a general rule, you should use the IN operator when you want to filter rows based on a specific list of values. Use EXISTS when you want to check for the existence of rows that meet certain conditions in a subquery.

Conclusion

In today's blog we learned how to use the EXISTS operator as well as how to decide whether to use EXISTS or IN.

Interested in giving Navicat Premium 17 a try? You can download it for a 14-day fully functional FREE trial. It's available for Windows, macOS, and Linux operating systems.