Subqueries Overview inner · outer · WHERE · FROM

Subqueries — Introduction

A query inside another query. Learn where subqueries can sit, how they classify by what they return and how they relate to the outer query, and how to pick between subquery and JOIN.

What is a subquery?

A subquery is a query placed inside another query. The inner query — wrapped in parentheses — runs first; its result is then used by the outer query. The outer one is called the main query or parent query; the inner one is the subquery or inner select.

Subqueries solve a specific class of problem: when you need a value (or a set of values) from one query to drive another. "Show me employees who earn more than the average" is the classic example — you need the average first, then a filter that uses it.

How a subquery flows

Where you can put a subquery

Location	Common use
WHERE clause	Filter rows by a value computed from another query.
HAVING clause	Filter groups by an aggregated comparison.
SELECT list	Compute a single value to display next to each row.
FROM clause	Use the result as if it were a table (an "inline view").
INSERT / UPDATE / DELETE	Drive which rows are affected.

Subquery flavours

Subqueries get classified along two axes — by what they return, and by how they relate to the outer query.

Flavour	Returns	Operators
Scalar / single-row	One value	`=` `<` `>` `<=` `>=` `<>`
Multiple-row	Many rows, one column	`IN`, `ANY`, `ALL`, `EXISTS`
Multiple-column	Rows with many columns	`IN`, `NOT IN` with row constructors
Correlated	Re-runs per outer row	References outer columns inside
Nested	Subquery inside another subquery	Multiple levels deep
Inline view	A whole table-like result in FROM	Treated as a table

Each flavour has its own page in this section. The naming might look overlapping — a correlated subquery can also be a scalar subquery, for instance — but each page focuses on one specific aspect.

Generic syntax

SELECT column_list
FROM table_name
WHERE column [operator] (
    SELECT column
    FROM other_table
    WHERE condition
);

A simple example

SQL

-- Employees who earn more than the company average
SELECT first_name, salary
FROM   employees
WHERE  salary > (SELECT AVG(salary) FROM employees);

The inner SELECT AVG(salary) FROM employees runs first, returns a single number, and the outer query then filters every employee against that number.

The rules

A subquery must be enclosed in parentheses.
A subquery used with single-row operators (=, <, etc.) must return only one row. Returning more raises an error.
ORDER BY is not allowed inside a subquery (PostgreSQL allows it but it's usually pointless — the outer query may resort anyway).
A subquery in the SELECT list must return only one column and one row.
You can nest subqueries arbitrarily deep — though more than two or three levels is hard to read.

Subquery vs JOIN

Many subquery problems can be rewritten as joins, and many join problems as subqueries. Each style has a moment where it reads more clearly:

A subquery reads naturally when the inner result is a single value or a set, and you want to filter by it.
A JOIN reads naturally when you actually need columns from both tables in the output.

The optimizer is usually clever enough to make either form fast — pick whichever is easier to read and maintain.

Where to go next

Single-row subqueries — return one value, used with =, <, >.
Multiple-row subqueries — return many rows, used with IN, ANY, ALL.
Multiple-column subqueries — return rows with several columns each.
Correlated subqueries — the inner query depends on the outer row.
Scalar subqueries — the special case of "exactly one value."
Nested subqueries — subqueries inside subqueries.
Inline subqueries — a subquery used in FROM like a table.