What is a SQL subquery?
A SQL subquery is a SELECT statement nested inside another query — in the WHERE clause, the FROM clause, or the SELECT list — that the outer query uses as a value, a set, or a derived table. An uncorrelated subquery stands on its own and runs once, independent of the outer query; a correlated subquery references a column from the outer query and is logically re-evaluated once per outer row. Both are plain Postgres SQL with no special syntax beyond nesting a SELECT where a value or table is expected.
1.Uncorrelated subqueries
An uncorrelated subquery does not reference anything from the outer query, so Postgres can run it once and reuse the result. This is the common IN-list pattern: find customers who placed at least one order over $100.
select id, name
from customers
where id in (
select customer_id
from orders
where total_cents > 10000
);2.Correlated subqueries
A correlated subquery references a column from the outer row (c.id below), so it reads as "run once per outer row" even though Postgres can often rewrite it into a single join-like plan. This finds every customer who has placed at least one order, without duplicating a customer row per order the way a plain join would.
select c.id, c.name
from customers c
where exists (
select 1
from orders o
where o.customer_id = c.id
);3.Subqueries in the SELECT list and FROM clause
A scalar subquery in the SELECT list returns one value per outer row (and must return at most one row). A subquery in the FROM clause is a derived table — it produces rows the outer query can filter, join, and group like any other table.
-- scalar subquery: one value per row
select c.id, c.name,
(select count(*) from orders o where o.customer_id = c.id) as order_count
from customers c;
-- derived table: a subquery used as a FROM source
select region, avg(order_count) as avg_orders_per_customer
from (
select c.id, c.region, count(o.id) as order_count
from customers c
left join orders o on o.customer_id = c.id
group by c.id, c.region
) per_customer
group by region;4.Performance notes
An uncorrelated subquery in an IN-list runs once, so it rarely costs more than the join it is standing in for. A correlated subquery is the one to watch: written naively it reads as "once per outer row," and on a large outer set that is real work unless Postgres rewrites it into a semi-join or anti-join plan, which it usually can for EXISTS/NOT EXISTS and IN/NOT IN — confirm with EXPLAIN ANALYZE rather than assuming. Index the column the correlated subquery filters on (orders.customer_id above), since that index is what makes the per-row lookup cheap. Prefer EXISTS over IN when the subquery can return duplicates or when NULLs are possible: NOT IN excludes all rows if the subquery returns even one NULL, which is a common correctness bug, while NOT EXISTS has no such trap. When a correlated subquery only re-derives a value you already need as a set, an equivalent JOIN or CTE is often clearer and gives the planner the same information. Try these queries on a free Scribase Postgres project and compare the EXPLAIN ANALYZE output for the join and subquery forms yourself.
Common questions
What is the difference between a correlated and an uncorrelated subquery?
An uncorrelated subquery does not reference the outer query and runs once on its own. A correlated subquery references a column from the outer row, so it is logically evaluated per outer row, even though Postgres can often rewrite it into a single-pass join plan.
Is EXISTS faster than IN?
For a well-indexed correlated check they usually produce the same or a very similar plan — confirm with EXPLAIN ANALYZE. The bigger reason to prefer EXISTS/NOT EXISTS over IN/NOT IN is correctness: NOT IN silently excludes every row when the subquery returns a NULL, which NOT EXISTS does not do.
Should I use a subquery or a JOIN?
Use a JOIN when you need columns from both tables in the result. Use a subquery (often with EXISTS or IN) when you only need to filter or check existence, since it avoids duplicating outer rows and usually reads more clearly for that case. Postgres often plans both the same way.
Can a subquery return more than one row?
A subquery used with IN, EXISTS, or ANY/ALL can return any number of rows. A scalar subquery, like one used directly in a SELECT list or compared with =, must return at most one row and one column, or Postgres raises an error at execution time.
Ready to try it?
Start a free project and follow this guide against a real backend, or move an existing project in with a dry run first.