Scalar subquery

Use a single-value subquery.

A subquery returning a single value is called scalar. It is used as one value in comparisons.

SELECT name FROM products
WHERE price = (SELECT MAX(price) FROM products);

This query returns the most expensive product.

name is selected from products. WHERE keeps only the rows where price = (SELECT MAX(price) FROM products) holds.

Scalar subquery — details

A scalar subquery returns one row and one column. Multiple values cause an error.

For example, (SELECT MAX(price) FROM products) returns a single number, so it can be compared with =; if several rows come back you need IN.

Find the most expensive product with a (SELECT MAX(price) FROM products) subquery.

Show example solution

SELECT name FROM products
WHERE price = (SELECT MAX(price) FROM products);

Add MAX

SELECT name FROM products
WHERE price = (SELECT ____(price) FROM products);

Show answer

SELECT name FROM products
WHERE price = (SELECT MAX(price) FROM products);