The SQL EXISTS Operator

Using the SQL EXISTS clause allows us to create complex queries in a simple way. Learn the pros and cons of the EXISTS operator in this article.

In SQL, the EXISTS operator helps us create logical conditions in our queries. Essentially, it checks if there are any rows in a subquery. We’ll show you EXISTS syntax, provide some usage examples, and then give you several exercises to practice on.

If you’re just getting started with SQL or need a SQL refresher, I recommend the course SQL Basics. It includes SQL topics ranging from basic SELECTs to complex topics like joins, aggregations, and subqueries. It’s a great way to build, refresh, or expand your SQL skills.

EXISTS Operator Syntax

You’re probably familiar with SQL operators like =, >, <, and LIKE. All these operators can be used to create logical conditions that will return TRUE or FALSE. Common condition examples include:

WHERE Employee_id = 10345
WHERE Price < 1000
WHERE Name LIKE ‘John%’

The operators =, <, and LIKE compare two elements or operands. This is the reason why they are called binary operators.

				WHERE EXISTS ( subquery )

Now let’s see the entire SQL query to determine where the EXISTS condition can be placed:

SELECT columns
FROM table1
WHERE EXISTS (SELECT columns FROM table2);

The EXISTS operator is used to create boolean conditions to verify if a subquery returns row(s) or an empty set.  When its subquery returns at least one row, EXISTS returns TRUE. It doesn’t matter how many rows are returned or how many columns are in the subquery SELECT list. Only when the subquery returns 0 rows does EXISTS return FALSE. It is important to note that none of the rows returned by the subquery are shown in the final result.

Example Database: Luxury Boats and Cars

Let’s examine several example queries based on a sample database of a company that sells luxury cars and boats. The company keeps track of the cars and boats in the table product, which has the columns product_id, product_name, product_price, product_type.

The database has also a Client table with the columns client_id, client_name and country.

Finally, there is the sale table with the columns product_id, client_id, sale_date. (For simplicity’s sake, I omit the idea of having a table for purchase orders; in this kind of company, one customer normally buys just one product at a time.)

Examples of the SQL EXISTS Operator

Example 1: Finding Products Sold

For the first example query, suppose the marketing manager wants to know which cars and boats were sold between April 1 and April 15 of 2024. The query below does that:

SELECT product_name 
FROM product p1
WHERE EXISTS ( SELECT * FROM sale s 
               WHERE  s.product_id = p1.product_id 
               AND    s.sale_date >= ‘2024-04-01’ 
               AND    s.sale_date <= ‘2024-04-15’
             )

Results:

There are other ways to write this query without using the EXISTS operator. However, in order to explain how the EXISTS operator works, this is a good entry-level example.

The main SELECT is very easy; it goes to the table product to obtain the product_name column. However we don’t want all the product_names in the table. We want only those products which returns TRUE for the following condition:

WHERE EXISTS ( SELECT * FROM sale s 
               WHERE  s.product_id = p1.product_id 
               AND     s.sale_date >= ‘2024-04-01’ 
               AND     s.sale_date <= ‘2024-04-15’
             )

If we analyze the condition, the subquery returns all the sales records for a given product (p1.product_id) and for a specific time period (2024-04-01 to 2024-04-15). If the subquery returns records, then EXISTS will return TRUE. If the subquery returns no records, then EXISTS will return FALSE. Note that the subquery is executed many times – once for each row read by the external query.

These kinds of subqueries that are executed one time for each row processed in the main  query are called “correlated subqueries” and they have their particularities. If you want to go deeper on this topic, I suggest the article Correlated Subquery in SQL: A Beginner’s Guide.

Example 2: Using the NOT EXISTS Operator

Now suppose the manager wants the names of the vehicles that didn’t sell during the same period. This type of query – where we search for records that do not exist in the database – is excellent for the NOT EXISTS operator. After all, we are searching for elements that do not exist in the database. The query is as follows:

SELECT * FROM product p1
WHERE p1.product_type = ‘Car’
AND   NOT EXISTS ( SELECT * FROM sale s
                   WHERE s.product_id = p1.product_id
                   AND   s.sale_date >= ‘2024-04-01’ 
                   AND   s.sale_date <= ‘2024-04-15’
                 )

Results:

Once we understand how the EXISTS operator works in SQL, understanding NOT EXISTS is very simple; it’s the opposite. If EXISTS returns TRUE, then NOT EXISTS returns FALSE and vice versa.

In this example, the main query has a WHERE clause with two conditions. The first condition is to ask for products of the type ‘vehicle’. The second condition uses NOT EXISTS to keep only the products which did not have sales during the period 2024-04-01 to 2024-04-15. In other words, we look for products for which the subquery returns an empty result set – i.e. for which there are no sales in that period.

In some cases, we can solve this kind of query using NOT IN with a subquery. In this particular query, we can put in this WHERE clause:

WHERE p1.product_type = ‘Car’
AND   p1.product_id NOT IN (SELECT s.product_id 
                            FROM  sale s
               WHERE s.sale_date >= ‘2024-04-01’ 
                            AND   s.sale_date <= ‘2024-04-15’
                           )

However, there is a difference in the way the query is executed internally in the database. The NOT IN  subquery condition is executed once, while the NOT EXISTS subquery condition is executed one time per each row. That’s right; NOT EXISTS is a correlated subquery. Read the articles 5 SQL Subquery Examples and SQL IN Operator for more on these operators.

Example 3: Using EXISTS with NOT EXISTS

Next, we need a list of those clients who didn’t buy a boat during the summer 2023 but did buy a boat during the preceding winter (i.e. December 2022 to March 2023). The query to obtain that report is:

SELECT * 
FROM   client c1
WHERE  EXISTS ( SELECT * FROM sale s1 
                JOIN  product p1 ON p1.product_id = s1.product_id
                WHERE c1.client_id = s1.client_id
                AND p1.product_type = 'Boat'
                AND s1.sale_date >= '2022-12-21' 
                AND s1.sale_date <= '2023-03-20' -- winter
              )
AND    NOT EXISTS ( SELECT * FROM sale s2
                   JOIN  product p1 ON p1.product_id = s2.product_id 
                   WHERE c1.client_id = s2.client_id
                   AND p1.product_type = 'Boat' 
                   AND s2.sale_date >= '2023-6-21' 
                   AND s2.sale_date <= '2023-09-20' -- summer
                  ) ;

Results:

After the previous two examples, this example should not be too difficult to understand; it is the combination of both. The idea is to select the entire record (SELECT *) from the client table and then use an EXISTS to check that a boat has been purchased last winter. After that, we use NOT EXISTS to check that a boat has not been purchased in the past summer. Note that both subqueries have a JOIN between the sale and product tables because we need to use the columns product_type and sale_date in the WHERE conditions.

At this point, I would like to clarify something about correlated subqueries. We’ve mentioned that correlated subqueries are executed one time per each row candidate. This fact can impact the performance of the whole query, especially when we work with large tables.

In summary, the EXISTS operator (and correlated subqueries) is a powerful SQL resource for certain kinds of queries. However, we should avoid correlated subqueries if we are working with big tables.

Practicing the EXISTS Operator

As many other computer languages, learning SQL by doing exercises is the one of the best ways to gain skills. So in this section, I will show three EXISTS operator exercises of different complexities.

Exercise 1: Old Orders

Exercise: The owner of the company wants to know which products (cars or boats) were not ordered in the last 365 days.

Solution:

	 SELECT p1.product_name
       FROM  product p1
       WHERE NOT EXISTS ( SELECT 1 FROM sale s
                          WHERE  s.product_id = p1.product_id
                          AND    s.sale_date >= CURRENT_DATE - 365
                        );

Results:

Explanation: The main query uses the table product to obtain the product_name column. The WHERE clause of this query is the key part. It has a NOT EXISTS condition which evaluates to TRUE for those products that have not been sold in the last year. The condition s.sale_date >= CURRENT_DATE - 365 in the subquery obtains all the rows of the last year.

Note the subquery returns a constant ( 1 ). This is because the important fact about the subquery is how many rows it returns, not the content of the returned rows; thus, we can put a column or a constant like 1.

Exercise 2: Boat Engine Buyers

Exercise: Our company is famous for manufacturing an engine for boats and we have some clients who only buy this product. The marketing department wants to identify clients who only buy boat engines so they can target these clients in a marketing campaign.

Solution:

SELECT * FROM client c1
WHERE EXISTS ( SELECT * FROM sale s1  -- they buy boat engines
		   JOIN  product p1 ON p1.product_id = s1.product_id 
                WHERE c1.client_id = s1.client_id
                AND   p1.product_name = 'Boat engine'   
               )
AND NOT EXISTS ( SELECT * FROM sale s2  -- they never buy other product
                 JOIN  product p2 ON p2.product_id = s2.product_id 
		    WHERE c1.client_id = s2.client_id
                 AND p2.product_name <> 'Boat engine' 
                );

Results:

Explanation: This exercise has a WHERE clause with two conditions. The first condition uses an EXISTS operator to verify that the customer selected by the outer query has purchased a boat engine. The second WHERE  condition uses NOT EXISTS to verify that the same customer (the customer selected by the outer query) has never purchased any other type of product.

If you want to explore more subquery exercises, I recommend the article SQL Subquery Practice: 15 Exercises with Solutions.

Exercise 3: Never Bought

Exercise: The owner of the company wants a Never Bought report. The report should have only two columns: client_name and product_name. The content of the report should be the complement of the sale table. In other words, if a client never bought a product, then the pair formed by this client_name and this product_name must be in the report.

Solution: There are two approaches to solving this exercise:

• Using NOT EXISTS.
• Using the set operator EXCEPT (or MINUS).

We’ll show both approaches.

Solution using NOT EXISTS:

SELECT c1.client_name, p1.product_name
FROM client c1, product p1
WHERE NOT EXISTS ( SELECT 1 FROM sale s 
                   WHERE c1.client_id = s.client_id
                   AND   p1.product_id = s.product_id  
                 )

Partial Query Results:

Explanation: The first point to understand is the result of this part of the query:

SELECT c1.client_name, p1.product_name
FROM client c1, product p1

As we are not properly joining the tables client and product, the result is a cartesian product – a set of all the possible <client_name, product_name> pairs.

Once we have all the possible pairs, we proceed to discard those pairs in the sale table using the NOT EXISTS operator.

Solution using EXCEPT:

SELECT c1.client_name, p1.product_name
FROM client c1, product p1
EXCEPT
SELECT client_name, product_name 
FROM sale s
JOIN product p ON p.product_id  = s.product_id
JOIN client c ON c.client_id = s.client_id

Explanation: The first part of this approach is similar to the previous solution; we create all possible client–product pairs. Then, using the EXCEPT operator, we remove the pairs which are in the sale table. This obtains the pairs we are looking for.

Here’s the difference: In the second part of the EXCEPT, we need to JOIN the sale table with the product and client tables.

Continue Expanding Your SQL EXISTS Operator Skills

In this article, we covered the EXISTS and NOT EXISTS operators with several examples. We also explained the pros and cons of correlated subqueries. For readers who want to increase their SQL skills, I recommend the SQL Practice track. You will find hundreds of exercises on SQL topics like JOINs, GROUP BY, HAVING, subqueries and even the EXISTS operator.

If you are just getting started with SQL or need a refresher, I recommend our SQL Basics course. When you increase your SQL skills, you invest in you!