Subqueries, EXISTS and IN: Which One When

A subquery is a query inside a query. You have probably written one already, copied from somewhere, and it worked. The risk is not that subqueries are hard. It is that three forms look alike, behave differently, and one of them fails silently when a NULL shows up.

The three forms are the scalar subquery (one value), IN (one column of values) and EXISTS (does at least one row exist). Each answers a different question, so start with the question.

Scalar subquery: one value

If the subquery returns exactly one row and one column, you can use it anywhere a value fits. Which products cost more than the average product?

SELECT name, price
FROM products
WHERE price > (SELECT avg(price) FROM products)
ORDER BY price DESC;

      name      | price
----------------+-------
 Backpack       | 29.90
 Desk Lamp      | 18.00
 Wireless Mouse | 15.50
 Laptop Sleeve  | 14.75
(4 rows)

The inner query runs once and gives a single number. If it can return more than one row, PostgreSQL stops you.

SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders);

ERROR:  more than one row returned by a subquery used as an expression

That error is useful. It tells you that the subquery returns a list, so you wanted IN, not equals.

IN: a list of values

Which customers have bought something from the bags category?

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT o.customer_id
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN products AS p ON p.product_id = i.product_id
  WHERE p.category = 'bags'
)
ORDER BY name;

   name
----------
 Ben Cruz
 Ella Tan
 Hugo Sy
(3 rows)

Ben Cruz bought a backpack in two different orders, and he appears once. IN asks whether the value is in the list, so a repeat in the list changes nothing. The same question written as a join returns one row per match.

SELECT c.name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS i ON i.order_id = o.order_id
JOIN products AS p ON p.product_id = i.product_id
WHERE p.category = 'bags'
ORDER BY c.name;

   name
----------
 Ben Cruz
 Ben Cruz
 Ella Tan
 Hugo Sy
(4 rows)

Four rows and Ben twice. That is the fan-out from the last lesson. When the question is "which customers", and not "which purchases", IN or EXISTS says so directly.

EXISTS: is there at least one row

EXISTS asks a yes or no question for each row of the outer query. The inner query usually refers to the outer row. That makes it a correlated subquery.

SELECT c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  JOIN products AS p ON p.product_id = i.product_id
  WHERE o.customer_id = c.customer_id
    AND p.category = 'bags'
)
ORDER BY c.name;

   name
----------
 Ben Cruz
 Ella Tan
 Hugo Sy
(3 rows)

Same three customers. What the inner query selects does not matter, because EXISTS only checks whether any row came back. SELECT 1 is the habit. The real strength of EXISTS is its negative form: customers who never ordered.

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

    name
------------
 Dan Lim
 Gina Ortiz
(2 rows)

This is the anti-join. It does the same job as a LEFT JOIN with WHERE o.order_id IS NULL, and it reads like the question.

A correlated subquery can also return a value for each row. Each customer's latest order date:

SELECT c.name,
       (SELECT max(o.order_date) FROM orders AS o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers AS c
ORDER BY c.name;

    name    | last_order
------------+------------
 Ana Reyes  | 2026-09-20
 Ben Cruz   | 2026-09-14
 Carla Diaz | 2026-09-02
 Dan Lim    |
 Ella Tan   | 2026-09-10
 Finn Go    | 2026-09-12
 Gina Ortiz |
 Hugo Sy    | 2026-09-25
(8 rows)

Dan Lim and Gina Ortiz get an empty value, because max of no rows is NULL. Keep this form for a single value per row. If you need several columns from the other table, use a join or the LATERAL form from the tutorials.

NOT IN and the NULL that empties your result

Here is the trap. Which customers live in a city different from the cities of customers 2 and 4?

SELECT name
FROM customers
WHERE city NOT IN (SELECT city FROM customers WHERE customer_id IN (2, 4))
ORDER BY name;

 name
------
(0 rows)

No rows, and no error. Customer 4 has no city, so the list is Cebu and NULL. city NOT IN ('Cebu', NULL) means "not Cebu and not NULL", and "not NULL" is never true. One NULL in the list empties the answer.

There are two honest fixes, and they give different answers because they mean different things. Remove the NULLs from the list and the customers with no city still drop out, because their own city is unknown.

SELECT name
FROM customers
WHERE city NOT IN (SELECT city FROM customers WHERE customer_id IN (2, 4) AND city IS NOT NULL)
ORDER BY name;

    name
------------
 Ana Reyes
 Carla Diaz
 Ella Tan
 Hugo Sy
(4 rows)

NOT EXISTS keeps them, because it only asks whether a matching row exists.

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM customers AS x
  WHERE x.customer_id IN (2, 4) AND x.city = c.city
)
ORDER BY c.name;

    name
------------
 Ana Reyes
 Carla Diaz
 Dan Lim
 Ella Tan
 Gina Ortiz
 Hugo Sy
(6 rows)

Six rows now, including Dan Lim and Gina Ortiz. Neither is "wrong". You decide whether a customer with no city is outside Cebu. The point is to decide, instead of letting the NULL decide for you.

Which one when

PostgreSQL usually plans IN and EXISTS as the same semi-join, so pick the one that reads best and check with EXPLAIN (the last section of this course) if a query is slow. NOT IN is the one that cannot be rewritten that way, because of the NULL rule. This part is PostgreSQL behaviour. The NULL rule itself is standard SQL.

Common mistakes

• Using = with a subquery that can return many rows.
• Using NOT IN on a column that allows NULL.
• Joining when the question is "which customers", then getting repeats.
• Selecting several columns in an IN subquery. It must return one column.
• Forgetting the link to the outer row in a correlated subquery, so EXISTS is true for everyone.

If you skip this, you will write a NOT IN that works for a year and returns nothing the day someone adds a customer with no city. Which of your queries has a NOT IN, and could its list ever contain a NULL?