Scalar Subqueries in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Aggregate Functions (COUNT, SUM, AVG, MIN, MAX), WHERE Clause and Comparison Operators
Builds toward Subqueries in WHERE (IN, EXISTS, ANY, ALL), Derived Tables (Subqueries in FROM)
What are Scalar Subqueries in SQL?
A scalar subquery is a complete SELECT statement embedded inside another query that returns exactly one value — one row, one column.
You're building a product pricing report. You want to see each product's price alongside the overall average price, so you can tell at a glance which products are above the average and which are below. The average is a single number computed from the whole products table. The product details come from row-by-row reading. Both need to appear in the same result. A scalar subquery lets you compute that single number inside the query that reads the product rows.
The inner query runs first, produces one value, and that value slots into the outer query wherever a number or column reference would go.
How do you compare a row against a table-wide average?
The most common use: compare each row against a table-wide aggregate. Products priced above average:
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)The subquery computes the average across all products. The outer query uses that number as the right side of the comparison. SQL runs the subquery once and applies the result to every row.
Can you put a subquery in the SELECT list?
You can put a scalar subquery in the SELECT list to add a computed column:
SELECT name, price, (SELECT AVG(price) FROM products) AS avg_price
FROM productsEvery row gets the same avg_price — the table-wide average — sitting next to the product's own price. One column from the table, one column from the subquery.
What makes a scalar subquery correlated?
Scalar subqueries can also be correlated — referencing a value from the outer query:
SELECT p.name, p.price, (SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id) AS category_avg FROM products p
The inner query re-runs for each outer row, using that row's category_id. Each product gets the average price for its own category — not the table-wide average. This is more expensive than a fixed scalar subquery because it runs once per row instead of once total.
Why does a subquery return more than one row error happen?
The one thing that trips people up: a scalar subquery that returns more than one row causes an error.
A subquery like (SELECT price FROM products WHERE category_id = 3) might return dozens of rows. SQL can't place dozens of values in a single column slot and raises an error. Only queries that are guaranteed to return one row — typically ones using aggregate functions like AVG, MAX, MIN, or COUNT(*) — are safe as scalar subqueries.
A scalar subquery in WHERE returns 5 rows instead of 1. What happens?
Practice Scalar Subqueries in SQL
Brightlane's pricing team wants to see every product in the catalogue alongside the overall average price across all products.
Write a query to return the product name, its price, and the overall catalogue average price for every product.
Assumptions:
- The
productstable contains every product in the catalogue. - The overall average is a single number — the same value should appear in the third column of every row.
- The average is computed over the entire
productstable, independent of the row being returned.
Output:
- One row per product, with columns
name,price, andavg_price.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Scalar Subqueries practice problems
Write a query to return the product name, its price, and the overall catalogue average price for every product.
Write a query to return the name and price of every product whose price exceeds the overall average.
Write a query to return the employee ID and salary amount for every above-average salary record.
Write a query to return the name and price of every product whose price equals the minimum price in the catalogue.
Write a query to return the order ID, total amount, and the overall order count for every order record.
Write a query to return the user ID, the conversion amount, and the combined total for every conversion record.
Write a query to return the count as a single number named cheaper_than_max.
Write a query to return the product name, price, and that product's own-category average price for every product.
Write a query to return the product name and price for every product that exceeds its own category's average price.
Start learning to practice all 9 Scalar Subqueries problems, with instant grading and mastery tracking.
Common questions about Scalar Subqueries
What does a scalar subquery return when it finds no rows?
NULL, with no error. That is the quiet failure mode: a filter that matches nothing gives you NULL rather than a complaint, and any comparison against that NULL then drops every row without saying why.
Where can a scalar subquery be used?
Anywhere a single value would go: in the SELECT list as a column, on either side of a WHERE comparison, or inside a larger expression. The requirement is not where it sits but what it returns, which has to be one row and one column.
Does a scalar subquery run once or once for every row?
Once, if it does not mention the outer query. The moment it references a column from the outer row it becomes correlated and runs again for each one, which is the difference between a table-wide average and a per-category one.
