Subqueries

A subquery is a SELECT statement nested inside another query. They’re one of the most flexible tools in SQL — you can use them in SELECT, FROM, WHERE, and HAVING clauses to express logic that a single flat query can’t capture. The tradeoff is that some subquery forms (particularly correlated subqueries) can be slow on large tables, so it’s worth knowing which patterns to reach for and when a JOIN or CTE would serve better.

Scalar Subqueries in SELECT

A scalar subquery returns exactly one row and one column. You can place it anywhere a single value is expected — including the SELECT list, which lets you compute a per-row summary from another table without a JOIN.

-- Show each order alongside the customer's lifetime total
-- The subquery runs once per row in the outer query
SELECT
  id,
  total,
  (SELECT SUM(total) FROM orders o2 WHERE o2.customer_id = o.customer_id) AS lifetime_value
FROM orders o;

This is a correlated subquery — it references o.customer_id from the outer query, so it runs once per row. For large tables, a JOIN with GROUP BY is usually faster.

Subqueries in WHERE with IN

The most common subquery pattern is using IN to filter rows based on values from another table. It reads naturally — “give me customers whose id appears in the list of ids that placed recent orders.”

-- Find customers who placed an order in the last 7 days
SELECT name
FROM customers
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE created_at >= NOW() - INTERVAL '7 days'
);

Caution with NOT IN and NULLs: If the subquery returns any NULL value, NOT IN returns no rows at all (because value NOT IN (1, 2, NULL) evaluates to NULL, never TRUE). Use NOT EXISTS instead when the subquery might return NULLs.

-- Safe alternative: customers who never ordered
SELECT name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS and NOT EXISTS

EXISTS returns TRUE as soon as the subquery finds one matching row and stops scanning — it never reads the entire inner table. This early-exit behavior makes it significantly faster than IN for large datasets, and it handles NULLs safely because it never compares values directly.

-- Customers who have at least one order over $500
-- EXISTS stops at the first match rather than collecting all matches
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1               -- the value returned doesn't matter, only whether a row exists
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.total > 500
);

The SELECT 1 is conventional — EXISTS only cares whether any row is returned, not what columns it contains.

Subqueries in FROM (Derived Tables)

A subquery in FROM acts like a temporary table for the duration of the query. This is useful when you need to pre-aggregate or pre-filter data before joining it to something else — you can’t reference an aggregate result in the same SELECT where it was computed, but you can wrap it in a derived table and filter the result.

-- Average of per-customer order counts
-- The inner query computes the count per customer;
-- the outer query averages those counts
SELECT AVG(order_count) AS avg_orders_per_customer
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) AS customer_stats;  -- derived tables must have an alias

You must give the subquery an alias (customer_stats above). In PostgreSQL 16+ the alias is optional in some cases, but it’s good practice to always include one.

Correlated Subquery Example

Finding the most recent order per customer is a classic correlated subquery pattern. It’s elegant but slow on large tables because the subquery re-executes for every row in the outer query.

-- For each order, check if it's the most recent one for that customer
SELECT *
FROM orders o
WHERE created_at = (
  SELECT MAX(created_at)
  FROM orders o2
  WHERE o2.customer_id = o.customer_id  -- correlates to the outer query
);

This works but is O(n²) — for each row in orders, it runs another scan of orders. The window function approach (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC)) is much faster on large tables.

LATERAL Joins

LATERAL allows a subquery in FROM to reference columns from tables listed earlier in the FROM clause — something ordinary subqueries cannot do. Think of it as a correlated subquery that’s allowed to sit in the FROM position, which enables more complex patterns like “top N per group.”

-- Latest 3 orders for each customer
-- The LATERAL subquery can reference c.id from the outer FROM clause
SELECT c.name, recent.id, recent.total, recent.created_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE customer_id = c.id   -- references the outer table
  ORDER BY created_at DESC
  LIMIT 3
) AS recent;

LATERAL is essentially a correlated subquery in the FROM position. It’s very useful for “top-N per group” problems and calling set-returning functions per row.

Subquery vs JOIN vs CTE: When to Use Which

Each approach has a natural home. The query planner often transforms subqueries into joins automatically, but understanding the intent of each form helps you write queries that are both correct and readable.

When a subquery and a JOIN produce the same result, the query planner often transforms one into the other automatically. Profile with EXPLAIN ANALYZE to confirm which is faster for your specific data.