By the end of this lesson you can write a scalar subquery that returns exactly one value, use it in WHERE and SELECT, and predict what happens when it returns no rows or too many.
Why it matters: Business questions are often two questions in one: “which products cost more than average?” needs the average first. A scalar subquery lets you answer both in one statement that stays correct when the data changes, instead of copying a number from one query into another.
Dataset: Wadi Market online store. Examples run in the editor on the right.
So far every query you wrote did its work in one pass: read rows, filter, group, sort. Many real questions need two steps. “Which products are priced above the average price?” First you need the average; only then can you compare each product with it.
The beginner's way is to run two queries: compute the average, copy the number, paste it into a second query. It works once, but the number goes stale the moment a price changes, and nobody reading the second query knows where 580.51 came from.
A subquery is a complete SELECT written inside another statement, in parentheses. PostgreSQL runs it and uses its result inside the outer query. Subqueries come in three shapes, and the shape decides where you can use them:
| The subquery returns | Called | Used like | This track |
|---|---|---|---|
| one row, one column | scalar subquery | a single value | this lesson |
| one column, many rows | a list | the right side of IN | lesson 2 |
| many rows and columns | a table | something in FROM | lesson 7 |
This lesson is about the first shape: a query that produces exactly one value.
SELECT columns
FROM table
WHERE column > (SELECT AGGREGATE(column) FROM table);SELECT ROUND(AVG(unit_price), 2) AS avg_list_price
FROM products;One row, one column: the average list price of all 61 products, 580.51 on this dataset. Because it returns a single value, this query can stand anywhere SQL expects a number. Running the inner query alone first is the best habit you can build with subqueries.
SELECT product_name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products)
ORDER BY unit_price DESC;PostgreSQL evaluates the subquery once, gets the average (580.51), and then filters products as if you had written WHERE unit_price > 580.51. 15 products pass, from the Falcon Book 16 Pro Laptop down. If prices change tomorrow, the query still answers the question correctly; a hard-coded number would not.
SELECT id, city, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);- 1. Inner query runs first: AVG(amount) over all 7 orders = 87.86
- 2. Its value replaces the subquery: WHERE amount > 87.86
- 3. Outer query filters: 3 orders are above average.
Why not just WHERE unit_price > AVG(unit_price)?
Try it and PostgreSQL answers aggregate functions are not allowed in WHERE. Remember the order in which a query is processed: WHERE looks at one row at a time, before any grouping happens, so there is no “average of all rows” available at that moment. The subquery is a separate query with its own FROM, so it can read the whole table and hand back a finished number.
Scalar subqueries in SELECT
A scalar subquery is just a value, so it can also appear in the SELECT list. That's how you compare every row with a company-wide figure: the difference from the average, or a share of a grand total.
SELECT product_name,
unit_price,
ROUND(unit_price - (SELECT AVG(unit_price) FROM products), 2) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC
LIMIT 10;The same average is computed once and subtracted from every product's price. Positive values are above average, negative below. Notice that the subquery's result appears in arithmetic like any number.
SELECT order_id, ordered_at, channel, status
FROM orders
WHERE ordered_at >= (SELECT MAX(ordered_at)::date FROM orders)
ORDER BY ordered_at;The subquery finds the latest order timestamp and casts it to a date (midnight of that day); the outer query keeps every order from that moment on. MAX, MIN, AVG, SUM and COUNT without GROUP BY are the classic sources of a scalar value.
The one-value rule
A scalar subquery must return at most one row and exactly one column. If it returns two rows, PostgreSQL stops with more than one row returned by a subquery used as an expression. That usually means you forgot the aggregate: (SELECT unit_price FROM products WHERE brand = 'Najm Mobile') returns one price per Najm product, and the database refuses to guess which one you meant. Decide what you want (the highest? the lowest? the average?) and say so with MAX, MIN or AVG.
If it returns two columns you get subquery must return only one column. Keep exactly one expression in the inner SELECT.