Subqueries, NOT IN and LATERAL

conceptThe concept

A scalar subquery returns one value and can appear wherever an expression can; it is an error if it returns more than one row. x IN (subquery) and EXISTS (subquery) test membership. A correlated subquery refers to the outer row and is logically run once per outer row; the planner often turns it into a join.

NOT IN is the trap. x NOT IN (1, NULL) means x <> 1 AND x <> NULL, and the second part is unknown, so the whole predicate is never true. One NULL in the subquery result empties the answer. NOT EXISTS does not have this problem.

LATERAL in FROM lets a subquery reference columns of tables listed before it. FROM customers c CROSS JOIN LATERAL (SELECT ... WHERE o.customer_id = c.id ORDER BY ... LIMIT 3) runs the inner query per customer. Use LEFT JOIN LATERAL ... ON true to keep rows where the subquery returns nothing.

whyWhy it matters

The NOT IN bug is a production classic: a report works for months until one NULL arrives in the subquery's column, and then it silently returns zero rows. LATERAL is the cleanest way to write per-group limits that use an index.

Use caseReal-world scenario

The support dashboard shows each platinum customer with their three most recent orders. LATERAL with ORDER BY and LIMIT expresses this directly, and with an index on (customer_id, created_at) each lookup is a short index scan.

Try itExamples

Run and edit these in the interactive lesson.

SELECT name, price,
       round(price - (SELECT avg(price) FROM products), 2) AS vs_average
FROM products
WHERE id IN (SELECT product_id FROM reviews WHERE rating = 5 GROUP BY product_id HAVING count(*) >= 15)
ORDER BY price DESC
LIMIT 5;

SELECT p.category, p.name, p.price
FROM products p
WHERE p.price > (SELECT avg(p2.price) * 2 FROM products p2 WHERE p2.category = p.category)
ORDER BY p.category, p.price DESC;

Employees who manage nobody. The subquery returns the CEO's NULL manager_id, so NOT IN finds nothing.

SELECT count(*) AS not_in
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

SELECT count(*) AS not_exists
FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.id);

SELECT c.name, recent.id AS order_id, recent.created_at::date AS day, recent.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.created_at, o.total
  FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY o.created_at DESC
  LIMIT 2
) AS recent
WHERE c.tier = 'platinum'
ORDER BY c.name, recent.created_at DESC
LIMIT 10;

SELECT c.id, c.name, last_order.created_at::date AS last_order
FROM customers c
LEFT JOIN LATERAL (
  SELECT created_at FROM orders o WHERE o.customer_id = c.id ORDER BY created_at DESC LIMIT 1
) last_order ON true
WHERE c.country = 'FR' AND c.id > 1800
ORDER BY c.id
LIMIT 8;

Avoid / preferDo this, not that

SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

SELECT e.name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.id);

SELECT c.name,
  (SELECT max(created_at) FROM orders o WHERE o.customer_id = c.id),
  (SELECT total FROM orders o WHERE o.customer_id = c.id ORDER BY created_at DESC LIMIT 1)
FROM customers c;

SELECT c.name, last.created_at, last.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT created_at, total FROM orders o
  WHERE o.customer_id = c.id ORDER BY created_at DESC LIMIT 1
) last ON true;

PitfallsCommon mistakes

• Using NOT IN against a column that can be NULL. Use NOT EXISTS.
• Writing a scalar subquery that can return two rows. It works in testing and fails in production with "more than one row returned by a subquery".
• Using CROSS JOIN LATERAL when the subquery might be empty and you still want the outer row. Use LEFT JOIN LATERAL ... ON true.

LimitsWhen not to use it

When a correlated subquery runs once per row of a large outer result, a join with GROUP BY is often far cheaper. Check the plan for a SubPlan executed thousands of times.

SecuritySecurity notes

Keep permission checks in the same statement as the data access (WHERE EXISTS (SELECT 1 FROM grants ...)), not in a separate earlier query, so a concurrent revoke cannot slip between the check and the read.

ChallengeTry it yourself

SELECT count(*) AS customers_last_cancelled
FROM customers c
CROSS JOIN LATERAL (
  SELECT status FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 1
) last
WHERE last.status = 'cancelled';

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Subquery expressions
• LATERAL subqueries