Subqueries: Scalar, Correlated, EXISTS, IN and the NOT IN Trap

In one line

Scalar subqueries return one value, correlated subqueries reference the outer row, and EXISTS/IN test membership. NOT IN with a subquery that returns any NULL yields no rows at all, so prefer NOT EXISTS for anti-joins. Modern planners usually turn IN and EXISTS into the same semi-join.

0/6 · 0%

Think of it like this

Asking "is anyone from Mumbai in this room?". EXISTS stops at the first Mumbaikar found. IN makes a list of cities first and checks against it. NOT IN goes wrong if one person's city is unknown: you can no longer say for sure that the city isn't on the list.

Key ideas

01
Scalar subquery in SELECT: (SELECT max(created_at) FROM orders o WHERE o.customer_id = c.id). Correlated; it may run per outer row unless the planner rewrites it. A LATERAL join is often clearer and faster for "top N per row".
02
x NOT IN (1, 2, NULL) is x <> 1 AND x <> 2 AND x <> NULL, and the last comparison is UNKNOWN, so the whole predicate is never TRUE. One NULL in the subquery silently returns zero rows.
03
NOT EXISTS has no such problem and is planned as an anti-join. Use it by default.
04
LATERAL lets a subquery in FROM reference earlier tables: "for each customer, their 3 latest orders" in one query using an index on (customer_id, created_at DESC).

Code & diagrams

subqueries.sqlsql

-- NOT IN trap
SELECT 1 WHERE 3 NOT IN (1, 2, NULL);      -- 0 rows
SELECT 1 WHERE 3 NOT IN (1, 2);            -- 1 row

-- customers whose referrer is not blacklisted (blacklist.customer_id may be NULL)
SELECT * FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.customer_id = c.referrer_id);

-- latest 3 orders per customer with LATERAL
SELECT c.id, o.id AS order_id, o.created_at
FROM customer c
CROSS JOIN LATERAL (
  SELECT id, created_at FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) o;

Interview problem

The problem

The report that returns nothing

SELECT * FROM product WHERE id NOT IN (SELECT product_id FROM discontinued) returned 0 rows after a data import, although most products are active. Explain and fix.

When it breaks

Correlated scalar subquery on a big result

What you see

The subquery executes once per outer row (millions of times); the report takes minutes.

Fix & prevent

Rewrite as a join with GROUP BY, a window function, or LATERAL with a supporting index; check with EXPLAIN (SubPlan nodes).

Explain it without notes

Why is NOT EXISTS preferred over NOT IN?

Practice

For each category, return the most expensive product using LATERAL.

Trade-offs

↔
Subqueries are readable; joins and window functions are often more efficient. Check the plan rather than guessing.

Done when you can

I can write correct semi/anti-joins and explain the NOT IN NULL trap.

Command Palette