Subqueries
Writing queries with subqueries
Sometimes the result of one query is needed by another query.
For example, imagine we want to find orders whose value is greater than the average order value.
We first need to calculate:
1average order valueand then use that value to filter the orders.
We can do both inside one SQL statement using a subquery.
What is a subquery?
A subquery is a query whose result is used by another query.
For example:
12345678910SELECT id, total_amountFROM ordersWHERE total_amount > ( SELECT AVG(total_amount) FROM orders)ORDER BY idLIMIT 10;The inner query is:
12SELECT AVG(total_amount)FROM ordersIt calculates the average order amount.
The outer query is:
12345SELECT id, total_amountFROM ordersWHERE total_amount > (...)It uses the value produced by the inner query.
The average is 412.10, so you should see:
123456789101112 id | total_amount-------+-------------- 40210 | 412.10 40211 | 412.11 40212 | 412.12 40213 | 412.13 40214 | 412.14 40215 | 412.15 40216 | 412.16 40217 | 412.17 40218 | 412.18 40219 | 412.19Conceptually:
12345678910111213orders │ ▼SELECT AVG(total_amount) │ ▼average order amount │ ▼outer query │ ▼orders above the average12345678910inner query outer querySELECT AVG(total_amount)FROM orders │ ▼ 412.10 ──────────────▶ WHERE total_amount > 412.10 │ ▼ matching ordersScalar subqueries
The previous example uses a scalar subquery.
A scalar subquery returns one column and no more than one row.
For example:
12SELECT AVG(total_amount)FROM orders;returns one value.
That allows us to use the result anywhere PostgreSQL expects a single value:
1234WHERE total_amount > ( SELECT AVG(total_amount) FROM orders)Another example is:
12345678SELECT id, total_amountFROM ordersWHERE total_amount = ( SELECT MAX(total_amount) FROM orders);The subquery calculates:
1largest order amountand the outer query returns orders with that amount:
123 id | total_amount-------+-------------- 88999 | 899.99What if a scalar subquery returns no row?
A scalar subquery can also return no rows.
For example:
12345SELECT ( SELECT email FROM customers WHERE id = -1) AS email;The inner query finds no customer.
In this case, the scalar subquery produces:
123 email------- NULLWhat if it returns more than one row?
A scalar subquery must not return multiple rows.
For example:
1234SELECT ( SELECT email FROM customers) AS email;is not valid because the inner query returns many customer emails.
PostgreSQL cannot turn all of those rows into the single value required by the outer expression.
It therefore reports:
1ERROR: more than one row returned by a subquery used as an expressionWhen a subquery is being used as a scalar value, ask:
Can this query return more than one row?
Using a subquery with IN
A subquery can also return multiple values.
Suppose we want orders placed by customers from India.
First, we can find the relevant customer IDs:
12345SELECT idFROM customersWHERE country = 'IN'ORDER BY idLIMIT 6;You should see:
12345678 id---- 3 8 13 18 23 28Then use those IDs in another query:
123456789101112SELECT id, customer_id, total_amountFROM ordersWHERE customer_id IN ( SELECT id FROM customers WHERE country = 'IN')ORDER BY idLIMIT 10;You should see:
123456789101112 id | customer_id | total_amount----+-------------+-------------- 2 | 3 | 10.02 7 | 8 | 10.07 12 | 13 | 10.12 17 | 18 | 10.17 22 | 23 | 10.22 27 | 28 | 10.27 32 | 33 | 10.32 37 | 38 | 10.37 42 | 43 | 10.42 47 | 48 | 10.47The subquery returns a set of customer IDs.
The outer query asks:
1Is this order's customer_id in that set?Conceptually:
12345678910111213customers │ ▼WHERE country = 'IN' │ ▼customer IDs │ ▼orders.customer_id IN (...) │ ▼matching ordersThe important difference from a scalar subquery is that the subquery used with IN can return many rows.
IN asks whether a value belongs to a set
Consider:
12345WHERE customer_id IN ( SELECT id FROM customers WHERE country = 'IN')The inner query produces:
12345381318...PostgreSQL then checks each outer customer_id against those values.
A useful mental model is:
12IN→ Is this value present in the set returned by the subquery?Subqueries in the SELECT list
A scalar subquery can also be used as a selected value.
For example:
1234567891011SELECT c.id, c.full_name, ( SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id ) AS order_countFROM customers cWHERE c.id <= 5ORDER BY c.id;You should see:
1234567 id | full_name | order_count----+------------+------------- 1 | Customer 1 | 28 2 | Customer 2 | 38 3 | Customer 3 | 39 4 | Customer 4 | 39 5 | Customer 5 | 38For each customer, the subquery calculates the number of matching orders.
Notice something important inside the subquery:
1o.customer_id = c.idc.id comes from the outer query.
The inner query therefore depends on a value from the current outer row.
This is called a correlated subquery.
We will explore correlated subqueries more closely in the next chapter when working with EXISTS.
Subqueries in FROM
A subquery can also appear in the FROM clause.
For example, suppose we first want to calculate total spending for every customer:
12345SELECT customer_id, SUM(total_amount) AS total_spentFROM ordersGROUP BY customer_id;We can use that result as the input to another query:
123456789101112SELECT customer_id, total_spentFROM ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) AS customer_totalsWHERE total_spent > 5000ORDER BY total_spent DESC;You should see:
123456789 customer_id | total_spent-------------+------------- 7 | 15830.60 4 | 15685.30 5 | 15390.40 2 | 15250.10 6 | 15230.50 3 | 15085.20 1 | 11820.00The inner query produces rows shaped like:
1customer_id | total_spentThe outer query then treats those rows as a table-like input named:
1customer_totalsA subquery used in FROM is often called a derived table.
Conceptually:
123456789101112131415orders │ ▼subqueryGROUP BY customer_idSUM(total_amount) │ ▼customer_totals │ ▼outer query │ ▼WHERE total_spent > 5000Joining a derived table
Because the result behaves like another table in the query, we can also join it.
For example:
123456789101112131415SELECT c.id, c.full_name, ct.total_spentFROM customers cINNER JOIN ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) AS ct ON c.id = ct.customer_idWHERE ct.total_spent > 5000ORDER BY ct.total_spent DESC;You should see:
123456789 id | full_name | total_spent----+------------+------------- 7 | Customer 7 | 15830.60 4 | Customer 4 | 15685.30 5 | Customer 5 | 15390.40 2 | Customer 2 | 15250.10 6 | Customer 6 | 15230.50 3 | Customer 3 | 15085.20 1 | Customer 1 | 11820.00The subquery calculates customer totals.
The outer query joins those totals to customer information.
Subquery or CTE?
A later section covers Common Table Expressions, where the same query is written like this:
123456789101112WITH customer_totals AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id)SELECT customer_id, total_spentFROM customer_totalsWHERE total_spent > 5000;Compare that with:
1234567891011SELECT customer_id, total_spentFROM ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id) AS customer_totalsWHERE total_spent > 5000;Both express the same broad idea: one query produces rows that another part of the statement uses.
A useful distinction is:
12345subquery→ write one query inside anotherCTE→ give an auxiliary query a name before the main queryFor a small intermediate query, a subquery may be perfectly readable.
For a larger statement with several logical steps, a CTE can sometimes make the structure easier to follow.
Choose the form that expresses the query clearly.
Correlation changes where values come from
Compare these two subqueries.
This one is independent of the outer row:
12345678SELECT id, total_amountFROM ordersWHERE total_amount > ( SELECT AVG(total_amount) FROM orders);The inner query doesn't refer to anything from the outer query.
But this one does:
123456789SELECT c.id, c.full_name, ( SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id ) AS order_countFROM customers c;The inner query uses:
1c.idfrom the outer query.
That makes it correlated.
Conceptually:
12345non-correlated subquery→ can be understood without values from the current outer rowcorrelated subquery→ depends on values from the current outer rowThis describes the logical dependency between the queries.
It does not mean PostgreSQL must physically execute the query using a particular row-by-row algorithm. The query planner can choose an appropriate execution plan.
Do not assume subqueries are slower than joins
Many SQL problems can be expressed in more than one way.
For example, some queries can be written using:
1234subqueryJOINEXISTSCTEThere is no useful rule that says:
1JOIN is always faster than a subqueryor:
1subqueries are always slowPostgreSQL's planner can transform and optimize queries in different ways.
Choose a query form that correctly expresses what you need.
If performance matters, inspect what PostgreSQL actually does with:
1EXPLAIN ANALYZErather than predicting performance only from the SQL syntax.
The core mental model
A subquery lets one query provide information to another.
That information might be:
1234567891011one value→ scalar subquerya set of values→ for example, INa set of rows→ subquery in FROMa result dependent on the outer row→ correlated subqueryThe important point is:
A subquery is a query whose result is used by another part of the SQL statement.