Lesson 10.1

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.

Pattern

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), or count(*) naturally fits because it collapses many rows into one value. Return two rows and DuckDB stops with More than one row returned by a subquery.

Preparing for an interview? The subquery interview questions put this chapter under pressure, with model answers to compare against.

Syntax reference

Need syntax details? See the SQL subquery and EXISTS reference

Schema · Garden Shop Table · products 9 columns · 24 rows

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Your task

Show every product whose price is above the overall average product price. Return product_name and price, most expensive first, breaking ties by product name.