Scalar subquery
- SQL course
- Intermediate
- Lesson 48/115
- 4 min
Use a single-value subquery.
Scalar 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.
Practice Scalar subquery
Find the most expensive product with a (SELECT MAX(price) FROM products) subquery.
Starter code
-- most expensive
SELECT name FROM products
WHERE price = (SELECTSample solution
SELECT name FROM products
WHERE price = (SELECT MAX(price) FROM products);Expected output
name Laptop
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add MAX
SELECT name FROM products
WHERE price = (SELECT ____(price) FROM products);SELECT name FROM products
WHERE price = (SELECT MAX(price) FROM products);Explanation: MAX(price) finds the highest price in the subquery; the outer query returns that product.
2 How many values does a scalar subquery return?
- Two
- As many as it likes
- One Correct answer
- Three
Explanation: A scalar subquery returns a single value.
3 Scalar subquery: match the terms
| Term | Meaning |
|---|---|
scalar |
single value |
MAX |
largest |
MIN |
smallest |
AVG |
average |