DocsSubqueriesScalar Subqueries

Scalar Subqueries

Subqueries

Concept

A scalar subquery is a subquery that returns exactly one row and one column — a single value. It can be used anywhere a single value is expected: in SELECT, WHERE, HAVING, or even in an expression. Scalar subqueries are enclosed in parentheses and executed for each row of the outer query (unless the optimizer can flatten them).

Common use cases include comparing each row against a global value (e.g., the average price), computing a derived column from a related table, or providing a default when no match exists. Because a scalar subquery must return exactly one value, it will error if it returns multiple rows.

Scalar subqueries in the SELECT list are convenient but can have performance implications. If the subquery is executed once per outer row, it creates an implicit nested loop. In many cases, a JOIN or a window function is a more efficient alternative.

Syntax

-- Scalar subquery in WHERE
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Scalar subquery in SELECT
SELECT
  name,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- Scalar subquery as a computed value
SELECT
  order_id,
  total_amount,
  total_amount / (SELECT MAX(total_amount) FROM orders) AS pct_of_max
FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Products priced above the average for their category
SELECT p.name, p.price, p.category
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price)
  FROM products p2
  WHERE p2.category = p.category
);

-- Each order compared to the overall average
SELECT
  o.order_id,
  o.order_date,
  o.total_amount,
  ROUND(o.total_amount - (
    SELECT AVG(total_amount) FROM orders
  ), 2) AS above_below_avg
FROM orders o
ORDER BY above_below_avg DESC;

Common Pitfalls & Tips

1A scalar subquery MUST return at most one row. If it returns multiple rows, the query fails with an error. Add LIMIT 1 or ensure your WHERE is restrictive enough.
2Scalar subqueries in SELECT can be slow because they may execute once per row. Consider rewriting as a JOIN or CTE for better performance.
3Be careful with NULL: if the scalar subquery returns NULL, comparisons with it yield UNKNOWN, which may silently exclude rows.

Practice scalar subqueries