DataslopeSubqueries
Queries inside queries — using the result of one SELECT as an input to another to answer layered questions.
So far each question has been one SELECT. But some questions are
layered: "show me orders bigger than the average order." You
cannot know the average until you compute it — so you write a query
inside a query. That inner query is a subquery.
The idea: a query as an input
A subquery is a SELECT wrapped in parentheses, whose result feeds
the outer query. PostgreSQL runs the inner query first, then uses
its answer.
Think of it as plugging the inner result into the outer query before the outer query runs.
A subquery that returns one value
The simplest subquery returns a single value — like an average —
which you compare against in a WHERE clause:
The inner SELECT AVG(amount) computes 40. The outer query then
behaves as if you had written WHERE amount > 40 — but you never had
to know the number yourself. Change the data and it stays correct.
Why not just type the number?
You could run the average separately and paste 40 into your query.
But then it is frozen — add an order and it is wrong. A subquery
recomputes every time, so the query is always right by construction.
A subquery that returns a list: IN
A subquery can also return a column of values, which pairs
naturally with IN. Here the inner query lists the ids of customers
who have ordered, and the outer query selects those customers:
The inner query produces the set {1, 2}; the outer query keeps
customers whose id is in that set — Ada and Grace. Linus, absent
from the list, is excluded. (This is one of several ways to express
the question; a join could do it too.)
Subqueries in different places
A subquery can appear in several parts of a statement, depending on the shape of its result:
- In
WHERE— the most common, as above: compare a column to a computed value or set. - In
FROM— use a query's result as if it were a table (a "derived table"), then query that. - In
SELECT— produce a single extra value alongside each row.
You will use the WHERE form constantly; the others appear as
questions grow more complex.
Check your understanding
What is a subquery?
A query that can only count rows.
A SELECT written inside another query, whose result is used by the outer query.
A faster version of a regular query.
A query that modifies the table structure.
Why use WHERE amount > (SELECT AVG(amount) FROM orders) instead of typing the average as a fixed number?
Because PostgreSQL forbids numbers in WHERE clauses.
Because the subquery recomputes the average from current data, so the query stays correct as data changes.
Because subqueries always run faster than arithmetic.
Because you cannot use AVG outside a subquery.
A subquery used with IN, like WHERE id IN (SELECT customer_id FROM orders), should return what shape of result?
A whole table with many columns.
A single column of values that the outer column is checked against.
Exactly one row and one column always.
No result; IN ignores the subquery.