EXISTS answers a yes-or-no question: does this subquery return any rows? It’s used in WHERE to keep the rows that have (or don’t have) something related in another table: customers with orders, products with no sales, users with no activity…

Syntax
SELECT columns
FROM table1 t1
WHERE [NOT] EXISTS (
    SELECT 1
    FROM table2 t2
    WHERE t2.column = t1.column
);

What the inner SELECT returns doesn’t matter: EXISTS only checks whether there are rows. By convention you write SELECT 1.

Customers with at least one order

The subquery is correlated: it uses C.customerId from the outer query, so it is evaluated for each customer.

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
customerId name lastName
1 Janice Haynes
2 Mavin Pettitt
3 Peter Davis
4 Helen Ward
5 Kimberly Lee

You could do the same with a JOIN, but Mavin and Helen would appear twice, once per order, and you’d need DISTINCT. EXISTS returns each customer once.

Customers without orders: NOT EXISTS

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
customerId name lastName
6 Jessie Good
7 Paul Williams

The NOT IN trap with NULL

This query looks like it should return the same as the previous one:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards

Run it: it returns no rows at all. The reason is the Balay Oven order, whose customerId is NULL. For SQL, 6 NOT IN (3, 2, 2, 1, 4, 4, 5, NULL) means “6 is different from 3 and from 2 and … and from NULL”, and a comparison with NULL is never true. A single NULL in the subquery is enough for NOT IN to return nothing.

NOT EXISTS doesn’t have this problem, because the condition O.customerId = C.customerId simply isn’t met for the order without a customer.

If you use NOT IN with a subquery, filter out the nulls: NOT IN (SELECT customerId FROM Orders WHERE customerId IS NOT NULL). Or better, use NOT EXISTS.

EXISTS with more conditions

The subquery can have any filter. Customers who bought a game console:

PostgreSQL in your browser · Ctrl+Enter to run · changes are rolled back afterwards
name lastName
Kimberly Lee
Mavin Pettitt

EXISTS, IN or JOIN

You need to… Use
Keep rows that have something related EXISTS or IN (without NULLs the result is the same)
Keep rows that have nothing related NOT EXISTS
Show columns from both tables JOIN
Compare with a fixed list of values IN ('a', 'b', 'c')

In SQL Server the optimizer usually turns EXISTS and IN into the same execution plan, so the choice is more about clarity and NULL behaviour than about speed.

Practice

  1. List the products whose customer has a bank account on file (bankAccount IS NOT NULL).
  2. Find the customers who have not placed any order after August 1, 2022.
  3. Fix the NOT IN query so it returns Jessie and Paul.