Scalar Subqueries

A scalar subquery is a subquery that is guaranteed — by its own logic — to return exactly one row and one column: a single value. Because it collapses down to just one value, a scalar subquery can be used anywhere a literal, a column, or an expression could be used: in a SELECT list, in a WHERE comparison, in an ORDER BY, or even inside another expression.

The classic way to guarantee a single value is to wrap the subquery around an aggregate function like AVG, MAX, MIN, SUM, or COUNT — these always collapse many rows into one number, so the result is automatically scalar.

Scalar Subquery in a SELECT List

Placing a scalar subquery in the SELECT list lets you show a row-level value side by side with a value computed across the whole table (or some other independent set of rows). Every output row gets the same subquery value, since the subquery does not depend on the outer row at all.

SQL

-- Show each product's price next to the company-wide average price
SELECT
  product_name,
  price,
  (SELECT AVG(price) FROM products) AS avg_price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products
ORDER BY diff_from_avg DESC;

Scalar Subquery in a WHERE Comparison

A scalar subquery also works naturally on the right-hand side of a comparison operator, since a comparison operator expects exactly one value on each side.

SQL

-- Find products priced above the company-wide average
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Find the single most recent order date, then find that order
SELECT order_id, customer_id, order_date
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Warning

A scalar subquery is only expected to return one row and one column — the database does not enforce that at write time. If a subquery you intended to be scalar unexpectedly returns more than one row when it actually runs, most databases (PostgreSQL, MySQL, SQL Server, and others) raise a runtime error such as "more than one row returned by a subquery used as an expression". This is a common source of confusion when debugging — the query looks perfectly valid, but fails only against certain data. Always aggregate (with AVG, MAX, etc.) or filter the subquery tightly enough that exactly one row can ever come back.

A Query That Looks Scalar but Isn't Safe

SQL

-- Looks fine at first glance...
SELECT customer_id, name
FROM customers
WHERE customer_id = (
  SELECT customer_id FROM orders WHERE total > 500
);
-- Runtime error the moment more than one order exceeds 500:
-- "more than one row returned by a subquery used as an expression"

-- Fix 1: aggregate down to a single value if that's the intent
SELECT customer_id, name
FROM customers
WHERE customer_id = (
  SELECT customer_id FROM orders ORDER BY total DESC LIMIT 1
);

-- Fix 2: use IN if you actually expect multiple matching rows
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE total > 500
);

Note

If a subquery might return zero rows, a scalar subquery evaluates to NULL rather than throwing an error — only returning more than one row is a runtime failure. Keep in mind that any comparison against NULL (such as price > NULL) is neither true nor false, so those rows are simply excluded from the result.

Scalar Subquery vs Other Subquery Forms

Form	Rows Returned	Typical Location
Scalar subquery	Exactly one row, one column	SELECT list, WHERE/HAVING comparisons
Multi-row subquery	Zero or more rows, one column	WHERE ... IN / ANY / ALL
Derived table	Zero or more rows, many columns	FROM clause
EXISTS subquery	Not used — only row presence matters	Standalone WHERE / HAVING check

A scalar subquery must resolve to one row and one column at runtime.
Aggregate functions (AVG, SUM, COUNT, MIN, MAX) are the most reliable way to guarantee a scalar result.
Zero rows returned becomes NULL; more than one row returned is a runtime error, not a silent bug.
Use IN, ANY, or ALL instead when a subquery might legitimately return multiple rows.