SQL Exists for Relationship Checks

“Find customers who have placed at least one order” sounds like a join. Write it as a join and you get Ana three times, because she has three orders. Then someone adds DISTINCT, the result looks right, and the query is now doing extra work to undo a multiplication it never needed. The question was never “give me each customer-order pair”; it was “does an order exist for this customer?”. SQL has a construct that asks exactly that.

The outputs and plans below are from PostgreSQL 16.15. The semantics, including the NOT IN trap, are standard SQL and behave the same in other databases.

Join answers a different question

CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int REFERENCES customers, total numeric);

INSERT INTO customers VALUES (1,'Ana'), (2,'Ben'), (3,'Chen'), (4,'Dee');
INSERT INTO orders VALUES (10,1,20), (11,1,35), (12,1,5), (13,3,80), (14,NULL,12);

Order 14 has no customer (a guest checkout). That NULL will matter later.

SELECT c.id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY c.id;

 id | name
----+------
  1 | Ana
  1 | Ana
  1 | Ana
  3 | Chen

A join produces one row per matching pair. That’s correct behaviour for “list each order with its customer name”, and wrong for “which customers have ordered”. Any aggregate on top inherits the duplication: sum(c.id) over that join gives 6, not 4.

With EXISTS:

SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY c.id;

 id | name
----+------
  1 | Ana
  3 | Chen

Each customer appears once whether they have one order or a thousand. The SELECT 1 inside doesn’t matter; EXISTS only checks whether the subquery returns any row, so SELECT * or SELECT o.id would behave identically. SELECT 1 is a convention that signals “I don’t care about the columns”.

Why DISTINCT is the wrong patch

SELECT DISTINCT c.id, c.name FROM customers c JOIN orders o ... returns the same two rows. It’s not wrong, but it’s a weaker query for three reasons:

1. It hides intent. A reader can’t tell whether DISTINCT is removing expected duplicates from the join or masking a bug somewhere else.
2. It deduplicates on every selected column. Add c.email and it still works; add o.total and the duplicates come back. Queries grow, and DISTINCT doesn’t grow with them.
3. It does extra work. The database builds all the pairs, then sorts or hashes them to remove duplicates. A semi join can stop looking at a customer’s orders after the first match.

The same argument applies to IN (SELECT ...). WHERE c.id IN (SELECT customer_id FROM orders) is also a semi join and returns each customer once. For the positive case, IN and EXISTS are interchangeable in PostgreSQL, and the planner produced the same plan for both on a larger copy of this data. The difference shows up with NOT.

The NOT IN trap

Now the reverse: customers who have never ordered. Ben and Dee. Here’s the version many people write first:

SELECT c.id, c.name
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

 id | name
----+------
(0 rows)

No rows at all. The guest order’s customer_id is NULL, so the subquery returns {1, 1, 1, 3, NULL}. For Ben, 2 NOT IN (1, 1, 1, 3, NULL) expands to 2 <> 1 AND 2 <> 1 AND 2 <> 1 AND 2 <> 3 AND 2 <> NULL. The last comparison is unknown, true AND unknown is unknown, and WHERE only keeps rows that are true. One NULL anywhere in the subquery empties the result. The NULL and three-valued logic guide walks through why.

NOT EXISTS doesn’t have this problem, because it asks a different question: “is there a row where o.customer_id = c.id?”. A NULL customer_id never equals anything, so it just doesn’t match, and it can’t poison the answer:

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
)
ORDER BY c.id;

 id | name
----+------
  2 | Ben
  4 | Dee

The anti-join written as LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL returns the same two rows and is fine too. Make sure you test a column that can’t be NULL in a real match (the primary key), or you’ll treat real matches as missing.

There’s a performance side to NOT IN as well. PostgreSQL can’t turn it into an anti join because of the NULL semantics. With 100,000 customers and 300,000 orders at the default work_mem of 4 MB, the NOT IN version used a non-hashed subplan that rescans the order list for every customer, and it hadn’t finished when a 120-second statement_timeout cancelled it. With work_mem at 64 MB it switched to a hashed subplan and took about 140 ms. The NOT EXISTS version took 92 ms at the default setting, as a Parallel Hash Anti Join. Correctness alone is enough reason to avoid NOT IN (subquery); the cliff in performance is a second one.

What the planner does with EXISTS

People sometimes worry that a correlated subquery runs once per outer row. For EXISTS with a simple correlation, PostgreSQL pulls it up into a join. On the larger dataset:

-- WHERE EXISTS (...)
 Hash Join
   Hash Cond: (c.id = o.customer_id)
   ->  Seq Scan on customers c
   ->  Hash
         ->  HashAggregate
               Group Key: o.customer_id
               ->  Seq Scan on orders o

-- WHERE NOT EXISTS (...)
 Hash Right Anti Join
   Hash Cond: (o.customer_id = c.id)
   ->  Seq Scan on orders o
   ->  Hash
         ->  Seq Scan on customers c

For EXISTS, the planner chose to deduplicate customer_id values first and then do an ordinary hash join, which is one of the ways it implements a semi join. Depending on sizes and indexes you’ll also see Hash Semi Join or Nested Loop Semi Join. For NOT EXISTS it’s a proper anti join. Either way, there’s no per-row subquery loop.

Choosing between them

• You need columns from the other table (order totals, dates): JOIN, and think about the grain you want.
• You need to filter by whether a related row exists: EXISTS.
• You need rows with no related row: NOT EXISTS, or the LEFT JOIN ... IS NULL pattern.
• You’re comparing against a fixed literal list (status IN ('paid', 'shipped')): IN is clearest, and NULLs in a literal list are rare and visible.

Conditions on the related row go inside the subquery: “customers with a refunded order in the last 30 days” is EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'refunded' AND o.created_at > now() - interval '30 days'). For a query that counts or sums related rows, you’re back in aggregation territory; see aggregation and result grain.

Interview practice: products never sold to a region

Tables: products(id, name), orders(id, region), order_items(order_id, product_id). List products that have never been sold in the EU region. A candidate wrote WHERE p.id NOT IN (SELECT oi.product_id FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.region = 'EU'). Is it correct? What would you write?

Answer

It’s correct only while order_items.product_id can never be NULL. If the column is nullable (say, items for deleted products are kept with product_id set to NULL), a single such EU line makes the query return no products at all, silently. It also risks the slow non-hashed subplan on large tables.

I’d write it with NOT EXISTS, putting both the correlation and the region condition inside:

SELECT p.id, p.name
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  JOIN orders o ON o.id = oi.order_id
  WHERE oi.product_id = p.id
    AND o.region = 'EU'
);

The join inside the subquery is fine: it can produce many rows for a product, but NOT EXISTS only cares whether there’s at least one. An index on order_items (product_id) lets PostgreSQL probe quickly per product if it chooses a nested loop. I’d test with a product sold only outside the EU (should appear), one sold in the EU (shouldn’t), and an EU order line with a NULL product_id (shouldn’t change anything).

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• SQL NULL and three-valued logic
• SQL joins and unexpected row multiplication
• Official docs: Subquery expressions: EXISTS, IN, NOT IN