SQL EXISTS and NOT EXISTS: Syntax and Examples

EXISTS answers a yes/no question for each row: "is there at least one matching row over there?" It never returns data from the subquery, only true or false. Here it finds the customers who have placed at least one order since 1 July 2024:

SELECT u.id, u.first_name, u.last_name
FROM users u
WHERE EXISTS (SELECT 1
              FROM orders o
              WHERE o.user_id = u.id
                AND o.order_date >= '2024-07-01')
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

For each user, the subquery looks for an order that belongs to that user and is recent enough. If it finds one, the user is kept; if not, the user is dropped. A customer with five recent orders still appears once.

You can also see the true/false value directly. SQLite shows true as 1 and false as 0:

SELECT EXISTS (SELECT 1 FROM orders WHERE total > 4900)  AS any_order_over_4900,
       EXISTS (SELECT 1 FROM orders WHERE total > 10000) AS any_order_over_10000;

▶ Run this query in the SQL Playground

Logically, the database takes the outer rows one at a time, plugs the current row's values into the subquery and runs it. As soon as one matching row turns up, EXISTS is true and the search for that row can stop. This early stop is called short-circuit evaluation, and it is why EXISTS does not care how many matches there are.

Real databases rarely run the subquery literally once per row. Most optimizers turn EXISTS into a semi-join, which keeps each outer row that has at least one match. The result is the same either way.

NOT EXISTS keeps the rows for which the subquery finds nothing. It is the standard way to find "missing" records: customers who never ordered, products that never sold, employees who manage nobody.

SELECT p.id, p.name, p.category
FROM products p
WHERE NOT EXISTS (SELECT 1
                  FROM order_items oi
                  WHERE oi.product_id = p.id);

▶ Run this query in the SQL Playground

Only 1 of the 120 products has never appeared on an order line. NOT EXISTS is also safe when the other column contains NULLs, which NOT IN is not.

EXISTS and NOT EXISTS together

Combine the two to describe a group precisely. These are the customers who have ordered but have never had an order cancelled or refunded:

SELECT COUNT(*) AS customers
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id)
  AND NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.user_id = u.id
                    AND o.status IN ('cancelled', 'refunded'));

▶ Run this query in the SQL Playground

The subquery can join tables of its own while staying correlated to the outer row. This finds the customers who have at least one order with a successful payment:

SELECT COUNT(*) AS customers_with_successful_payment
FROM users u
WHERE EXISTS (SELECT 1
              FROM orders o
              JOIN payments p ON p.order_id = o.id
              WHERE o.user_id = u.id
                AND p.status = 'success');

▶ Run this query in the SQL Playground

The join between orders and payments happens inside the subquery; o.user_id = u.id is still what ties it to each customer.

A condition inside the subquery

Any filter on the related rows goes inside the subquery. This lists the departments that have at least one employee earning 200,000 or more:

SELECT d.name, d.location
FROM departments d
WHERE EXISTS (SELECT 1
              FROM employees e
              WHERE e.department_id = d.id
                AND e.salary >= 200000)
ORDER BY d.name;

▶ Run this query in the SQL Playground

Because EXISTS produces true or false, it fits anywhere a condition does, including CASE WHEN. This labels each customer:

SELECT u.id, u.first_name,
       CASE
         WHEN EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'delivered') THEN 'Has a delivered order'
         WHEN EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) THEN 'Ordered, nothing delivered yet'
         ELSE 'Never ordered'
       END AS customer_state
FROM users u
ORDER BY u.id
LIMIT 8;

▶ Run this query in the SQL Playground

Several tools can answer "does a related row exist?". They differ in what they return and in how they treat duplicates and NULLs:

The JOIN version shows the duplicate problem. Joining users to orders gives one row per order, not per customer:

SELECT (SELECT COUNT(*) FROM users u JOIN orders o ON o.user_id = u.id) AS join_rows,
       (SELECT COUNT(*) FROM users u
        WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)) AS exists_rows;

▶ Run this query in the SQL Playground

Use EXISTS when you only need to filter, a JOIN when you need the other table's columns, and COUNT when the number itself is the answer. For a full comparison with IN, see IN vs EXISTS.

See SQL indexes for how an index changes these lookups.

Six errors, most of which run without any error message.

SELECT *
FROM users u
WHERE EXISTS (SELECT 1 FROM orders);

SELECT *
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id);

SELECT u.first_name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id);

SELECT u.first_name, o.id AS order_id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

SELECT *
FROM users u
WHERE EXISTS (SELECT 1 FROM orders u
              WHERE u.user_id = u.id);

SELECT *
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id);

WHERE (SELECT COUNT(*) FROM orders o
       WHERE o.user_id = u.id) > 0

WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id)

WHERE EXISTS (SELECT o.total FROM orders o
              WHERE o.user_id = u.id)

WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id
                AND o.total > 4000)

SELECT *
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

SELECT *
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r
                  WHERE r.manager_id = e.id);

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Count the customers who have written at least one review. Table: users, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS reviewers
   FROM users u
   WHERE EXISTS (SELECT 1
                 FROM reviews r
                 WHERE r.user_id = u.id);

   ▶ Run this query in the SQL Playground

   The correlated subquery looks for one review per customer.

2. 2Easy List the first 8 customers (by id) who have never ordered. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name, u.last_name
   FROM users u
   WHERE NOT EXISTS (SELECT 1
                     FROM orders o
                     WHERE o.user_id = u.id)
   ORDER BY u.id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   NOT EXISTS keeps the users for whom the subquery finds no order.

3. 3Medium List the products that have at least one 5-star review, cheapest first. Show 8. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT p.name, p.price
   FROM products p
   WHERE EXISTS (SELECT 1
                 FROM reviews r
                 WHERE r.product_id = p.id
                   AND r.rating = 5)
   ORDER BY p.price
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   The rating condition sits inside the subquery, next to the correlation.

4. 4Medium Count the employees who manage at least one other employee. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS managers
   FROM employees m
   WHERE EXISTS (SELECT 1
                 FROM employees e
                 WHERE e.manager_id = m.id);

   ▶ Run this query in the SQL Playground

   The same table appears twice with different aliases: m for the possible manager, e for the people who might report to them.

5. 5Medium Count the orders that have no payment with status "success". Table: orders, payments

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS unpaid_orders
   FROM orders o
   WHERE NOT EXISTS (SELECT 1
                     FROM payments p
                     WHERE p.order_id = o.id
                       AND p.status = 'success');

   ▶ Run this query in the SQL Playground

   This includes orders with no payment at all and orders whose payments all failed, are pending or were refunded.

6. 6Hard Count the customers who have bought at least one product in the Books category. Table: users, orders, order_items, products

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS book_buyers
   FROM users u
   WHERE EXISTS (SELECT 1
                 FROM orders o
                 JOIN order_items oi ON oi.order_id = o.id
                 JOIN products p     ON p.id = oi.product_id
                 WHERE o.user_id = u.id
                   AND p.category = 'Books');

   ▶ Run this query in the SQL Playground

   The subquery joins three tables; only o.user_id = u.id connects it to the customer.