Subquery

A subquery is a SELECT statement nested inside another SQL statement, enclosed in parentheses. PostgreSQL evaluates the inner query first and passes its result to the outer query. Subqueries let you break a complex data-retrieval problem into smaller, composable steps without creating intermediate tables.

Basic subquery syntax

The general pattern places the subquery inside the WHERE clause of an outer SELECT:

SELECT select_list
FROM table1
WHERE columnA operator (
  SELECT columnB
  FROM table2
  WHERE condition
);

The subquery is always wrapped in parentheses and runs before the outer query. The result is then used by the outer query's condition.

Single-value subquery with a comparison operator

When a subquery returns exactly one value (a scalar), you can use it directly with =, >, <, and other comparison operators. This example retrieves all cities in the United States without hard-coding the country ID:

SELECT city
FROM city
WHERE country_id = (
  SELECT country_id
  FROM country
  WHERE country = 'United States'
)
ORDER BY city;

Output:

city
-------------------------
Akron
Arlington
Augusta-Richmond County
Aurora
Bellevue
Brockton
Cape Coral
Citrus Heights
...

PostgreSQL first executes the inner query to retrieve country_id = 103, then uses that value to filter the outer query.

Multi-value subquery with IN

When a subquery can return more than one row, use the IN operator. This example finds all Action films by looking up their IDs in the film_category table:

SELECT film_id, title
FROM film
WHERE film_id IN (
  SELECT film_id
  FROM film_category
  INNER JOIN category USING(category_id)
  WHERE name = 'Action'
)
ORDER BY film_id;

Output:

film_id |          title
---------+-------------------------
     19 | Amadeus Holy
     21 | American Circus
     29 | Antitrust Tomatoes
     38 | Ark Ridgemont
     56 | Barefoot Manchurian
...

The subquery returns a list of film_id values for Action films. The outer query then filters the film table to only those IDs.

Tips for writing effective subqueries

• Use a scalar subquery (one that returns a single value) with comparison operators like =; use IN or ANY for subqueries that return multiple rows.
• If the subquery references the outer query's columns, it becomes a correlated subquery — PostgreSQL re-executes it for every row in the outer query, which can be slow on large tables.
• For performance-critical queries, compare the subquery approach against a JOIN using EXPLAIN ANALYZE — the planner sometimes produces a better plan with a join.
• Consider rewriting complex or repeated subqueries as CTEs (WITH clauses) to improve readability and make the query easier to test in isolation.

Reference: PostgreSQL documentation — Subqueries.