Subqueries

A subquery is a SELECT nested inside another SQL statement. PostgreSQL lets you use one almost anywhere a value, row, or table could otherwise appear — in WHERE, in FROM, in the SELECT list, and more. What the subquery is allowed to return depends on where it's used.

Scalar, row, and table subqueries

Kind	Returns	Used where
Scalar	A single value (one row, one column)	Anywhere a single value is expected — `SELECT` list, `WHERE`, comparisons
Row	A single row with multiple columns	Compared against a row constructor, e.g. `WHERE (a, b) = (SELECT ...)`
Table	Multiple rows and/or columns	`FROM` clause (as a derived table), or with `IN` / `EXISTS` / `ANY` / `ALL`

Subquery in WHERE

The most common spot for a subquery — filtering rows in the outer query based on something computed by an inner query.

orders, customers

SQL

-- Orders placed by customers in 'Germany'
SELECT *
FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'Germany'
);

Subquery in FROM (a derived table)

A subquery in FROM is treated like a temporary table for the rest of the query — it must be given an alias.

SQL

SELECT region, avg_order_value
FROM (
  SELECT region, AVG(total) AS avg_order_value
  FROM orders
  GROUP BY region
) AS region_stats
WHERE avg_order_value > 100;

Subquery in SELECT (scalar subquery)

SQL

SELECT
  c.id,
  c.name,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

This scalar subquery runs once per outer row, returning exactly one value each time — if it ever returned more than one row, PostgreSQL would raise a runtime error.

Correlated vs non-correlated subqueries

	Non-correlated	Correlated
References outer query?	No — self-contained	Yes — reads a column from the outer row
Evaluated	Once, then reused for every outer row	Once per outer row
Example	`WHERE total > (SELECT AVG(total) FROM orders)`	`WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)`

The scalar subquery example above is correlated: it references c.id from the outer query, so PostgreSQL conceptually re-runs it for each customer row. A non-correlated subquery is independent of the outer query and only needs to run once.

EXISTS / NOT EXISTS vs IN / NOT IN

EXISTS only checks whether the subquery returns any row at all — it doesn't care how many or what their values are, so the planner can often stop as soon as it finds a single match. This tends to make EXISTS / NOT EXISTS at least as fast as, and often faster than, IN / NOT IN for the same logical check — especially on large tables.

EXISTS — customers who have placed at least one order

SQL

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

NOT IN's NULL trap

NOT IN has a well-known gotcha: if the subquery's result contains even a single NULL, the entire NOT IN comparison evaluates to UNKNOWN for every row, and the outer query returns zero rows — silently.

Broken — returns no rows if any order has a NULL customer_id

SQL

SELECT c.*
FROM customers c
WHERE c.id NOT IN (
  SELECT customer_id FROM orders   -- if this contains NULL, the whole query breaks
);

This happens because x NOT IN (a, b, NULL) expands to x != a AND x != b AND x != NULL, and any comparison against NULL is UNKNOWN rather than TRUE or FALSE — which makes the whole AND chain UNKNOWN, so the row is excluded. The fix is to rewrite it with NOT EXISTS, which has no such NULL-handling trap because it never compares values directly:

Fixed — NOT EXISTS is immune to the NULL trap

SQL

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

Note

If you must use NOT IN, guard the subquery with WHERE customer_id IS NOT NULL to strip out the NULLs first. But reaching for NOT EXISTS from the start is simpler and avoids the pitfall entirely.

Tip

As a rule of thumb: prefer EXISTS / NOT EXISTS over IN / NOT IN whenever you're only checking for the presence or absence of a related row, rather than needing the actual list of matching values.

Scalar subqueries return one value; row subqueries return one row; table subqueries return many rows and/or columns.
Subqueries can appear in SELECT, FROM, and WHERE (among other places).
A correlated subquery references a column from the outer query and re-runs per outer row; a non-correlated subquery is self-contained.
NOT IN silently returns zero rows if its subquery result contains a NULL — NOT EXISTS avoids this trap.