Semi-joins and anti-joins: EXISTS and NOT EXISTS

Asking “is there any?”

“Which customers have placed an order?” is not asking for their orders. It asks, for each customer, a yes-or-no question: does any matching order exist? A join answers a different question, and returns one row per match.

EXISTS answers the yes-or-no question directly:

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

For each customer, the inner query looks for an order with that customer’s id. EXISTS is true as soon as it finds one; PostgreSQL does not need to look for the rest. Each customer appears once whether they have one order or ten. SELECT 1 is a convention: what the inner query returns does not matter, only whether it returns anything.

This pattern is called a semi-join: rows from one table, filtered by the existence of matches in another.

Asking “is there none?”

The opposite, an anti-join, finds rows with no match. Customers who have never ordered:

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

     name
---------------
 Elena Petrova
 Hugo Martin

And products nobody has bought:

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

      name
-----------------
 Cable organiser

You will also see anti-joins written as a left join that keeps only the unmatched rows:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

It returns the same two customers. NOT EXISTS says what you mean more directly, and avoids a trap with NOT IN covered in Subqueries and query composition.

A self join

A table can be joined to itself, with two aliases. Pairs of customers who live in the same city:

SELECT a.name AS customer, b.name AS same_city_as, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;

 customer | same_city_as | city
----------+--------------+------
 Asha Rao | Divya Iyer   | Pune

a.id < b.id stops each customer pairing with themselves and stops each pair appearing twice. Elena and Hugo both have NULL cities, but NULL = NULL is unknown, so they do not count as sharing a city.

Check yourself

1. Why does EXISTS return each customer once, when a join returns one row per order?

   Show answer

   EXISTS is a true-or-false test evaluated once per customer, which stops at the first matching order. A join outputs one combined row for every matching pair.

   I could explain this without looking

2. Write a query for customers who have never placed an order.

   Show answer

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

   I could explain this without looking

3. Why do Elena and Hugo not pair up in the same-city self join?

   Show answer

   Both cities are NULL, and NULL = NULL is unknown, so the join condition is not true for them.

   I could explain this without looking