SQL IN vs EXISTS: Differences, Performance and NULLs

Both queries below find the customers who have placed at least one order. The IN version builds a list of customer ids from orders and keeps the users whose id is in it. The EXISTS version asks, for each user, "is there an order row for this user?"

With IN

SELECT COUNT(*) AS customers_with_orders
FROM users
WHERE id IN (SELECT user_id FROM orders);

▶ Run this query in the SQL Playground

With EXISTS

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

▶ Run this query in the SQL Playground

Both return 114. For a positive check like this they are interchangeable, and most query optimizers turn both into the same plan (a semi-join: keep each outer row that has at least one match, without repeating it).

-- IN with a fixed list or a subquery
WHERE column IN (value_1, value_2, ...)
WHERE column IN (SELECT column FROM other_table WHERE ...)

-- EXISTS with a correlated subquery
WHERE EXISTS (SELECT 1
              FROM other_table o
              WHERE o.key = outer_table.key)

An IN subquery must return exactly one column. An EXISTS subquery can select anything, because only the presence of a row matters; SELECT 1 makes that intent obvious. The condition o.key = outer_table.key is what ties the EXISTS check to the current row. Without it, EXISTS is either true for every row or false for every row.

IN is also the natural choice for a short list of literal values, which EXISTS cannot express:

SELECT status, COUNT(*) AS orders
FROM orders
WHERE status IN ('shipped', 'delivered')
GROUP BY status;

▶ Run this query in the SQL Playground

Logically, IN works out the whole list first and then compares each outer row against it. EXISTS runs its subquery for each outer row and can stop at the first matching row, because one match is enough to make it true. That early stop is called short-circuit evaluation.

You can see the "per row" nature of EXISTS by asking more than one question at once. This labels each of the first users by whether they have ordered and whether they have written a review:

SELECT u.id, u.first_name,
       CASE WHEN EXISTS (SELECT 1 FROM orders o  WHERE o.user_id = u.id) THEN 'yes' ELSE 'no' END AS has_ordered,
       CASE WHEN EXISTS (SELECT 1 FROM reviews r WHERE r.user_id = u.id) THEN 'yes' ELSE 'no' END AS has_reviewed
FROM users u
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

This is the difference that changes results. Find the employees who manage nobody: their id never appears in anyone's manager_id. With NOT IN:

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

▶ Run this query in the SQL Playground

The answer is 0, which is wrong. The reason: 10 employees have no manager, so the subquery list contains NULL. id NOT IN (3, 7, NULL) means id <> 3 AND id <> 7 AND id <> NULL, and id <> NULL is never true. So no row passes.

SELECT COUNT(*) - COUNT(manager_id) AS null_manager_ids
FROM employees;

▶ Run this query in the SQL Playground

NOT EXISTS asks a different question, "is there an employee whose manager is this one?", and NULLs simply never match:

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

▶ Run this query in the SQL Playground

110 employees, the right answer. NOT IN gives the same result only if you filter the NULLs out of the list yourself:

SELECT COUNT(*) AS non_managers
FROM employees
WHERE id NOT IN (SELECT manager_id
                 FROM employees
                 WHERE manager_id IS NOT NULL);

▶ Run this query in the SQL Playground

There is no rule that one always wins. What usually holds:

Whatever the rule of thumb says, the plan on your own data is what counts. Run EXPLAIN (PostgreSQL, MySQL), EXPLAIN QUERY PLAN (SQLite) or look at the execution plan (SQL Server) for both versions. An index on the column the subquery matches on, here orders.user_id, helps both. See indexes.

A JOIN can answer "who has an order?" too, but it returns one row per matching order, so customers with several orders appear several times:

SELECT COUNT(*) AS joined_rows,
       COUNT(DISTINCT u.id) AS distinct_customers
FROM users u
JOIN orders o ON o.user_id = u.id;

▶ Run this query in the SQL Playground

The JOIN produces 200 rows for 114 customers, so you would need DISTINCT to get back to one row each. Use IN or EXISTS when you only need to filter; use a JOIN when you need columns from the other table, such as the order total.

When the question is "how many" rather than "whether", neither IN nor EXISTS is the tool. Count with a LEFT JOIN and GROUP BY:

SELECT u.id, u.first_name,
       COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name
ORDER BY u.id
LIMIT 6;

▶ Run this query in the SQL Playground

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

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);

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);

-- "customers who never ordered"
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id)

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

WHERE EXISTS (SELECT 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 > 1000)

SELECT DISTINCT u.id, u.first_name
FROM users u
JOIN orders o ON o.user_id = u.id;

SELECT u.id, u.first_name
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.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 products that have been ordered at least once, using IN. Table: products, order_items

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS ordered_products
   FROM products
   WHERE id IN (SELECT product_id FROM order_items);

   ▶ Run this query in the SQL Playground

   The subquery lists every product id that appears in an order line; IN keeps the products in that list.

2. 2Easy Answer the same question with EXISTS. Table: products, order_items

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS ordered_products
   FROM products p
   WHERE EXISTS (SELECT 1
                 FROM order_items oi
                 WHERE oi.product_id = p.id);

   ▶ Run this query in the SQL Playground

   Same result: the correlated subquery finds at least one order line per product.

3. 3Medium List the first 8 products (by id) that have never been reviewed. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

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

   ▶ Run this query in the SQL Playground

   NOT EXISTS keeps the products with no matching review row.

4. 4Medium Count the customers who have at least one order over 4,000. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS big_spenders
   FROM users u
   WHERE EXISTS (SELECT 1
                 FROM orders o
                 WHERE o.user_id = u.id
                   AND o.total > 4000);

   ▶ Run this query in the SQL Playground

   The extra condition on the order goes inside the subquery, next to the correlation.

5. 5Medium Count the customers who have ordered but never written a review. Table: users, orders, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS buyers_without_reviews
   FROM users u
   WHERE EXISTS     (SELECT 1 FROM orders o  WHERE o.user_id = u.id)
     AND NOT EXISTS (SELECT 1 FROM reviews r WHERE r.user_id = u.id);

   ▶ Run this query in the SQL Playground

   EXISTS and NOT EXISTS combine with AND like any other conditions.

6. 6Hard Show how many orders per status have no payment row at all. Table: orders, payments

   Show solution

   SQLite · runs in the Playground

   SELECT o.status,
          COUNT(*) AS orders_without_payment
   FROM orders o
   WHERE NOT EXISTS (SELECT 1
                     FROM payments p
                     WHERE p.order_id = o.id)
   GROUP BY o.status
   ORDER BY orders_without_payment DESC;

   ▶ Run this query in the SQL Playground

   NOT EXISTS filters the orders first; GROUP BY then counts the remaining ones per status.