SQL Scalar Subqueries
A scalar subquery returns one value, which makes it useful for row-by-row comparisons against a calculated benchmark.
A scalar subquery is a nested SELECT that returns one value: one row, one column. You can use that value anywhere SQL expects a single value, such as the right side of a comparison.
The pattern is useful when a row needs to be compared with a benchmark that also comes from the data. For example, "products whose price is above the average product price" needs the average first, then uses it as a filter.
select column_one, metric
from table_name
where metric > (
select avg(metric)
from table_name
)Keep scalar subqueries truly scalar
The inner query must return exactly one column and no more than one row. An aggregate such as avg(price), max(order_date), orcount(*) naturally fits because it collapses many rows into one value.
Schema · Garden ShopTable · products9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
Show the 6 most expensive products whoseprice is above the overall average product price. Returnproduct_name and price.
- Columns: product_name, price.
- Rows: the 6 most expensive products above the overall average price.
- The subquery returns one average price for the whole products table.