Scalar Subqueries
intermediatesubqueries
2 min read
What is a Scalar Subquery?
A scalar subquery 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 expressions.
Syntax
SELECT column,
(SELECT MAX(price) FROM products) AS max_price
FROM products;SELECT * FROM students
WHERE age = (SELECT MIN(age) FROM students);When to Use
- Comparing a column to a global aggregate (max, min, avg)
- Including a single computed value in every row
- Using a computed value in a HAVING clause
Key Points
- Single Value — Must return one row and one column. More rows cause a runtime error.
- NULL on Empty — If the subquery returns no rows, the result is NULL (not an error).
- Anywhere an Expression Works — Can appear in SELECT, WHERE, HAVING, JOIN conditions, etc.
- Optimization — Non-correlated scalar subqueries are computed once and cached.
- Error Prevention — Use LIMIT 1 or an aggregate to ensure at most one row is returned.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Scalar Subqueries Challenge
Write a query that solve this task: show each product with the overall max price.
Expected result
Each product with its price and the maximum price across all products.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is a Scalar Subquery?
Initializing database...Each run starts from fresh sample data.
More Examples
Find the youngest student
Use a scalar subquery to find the minimum age.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What if a scalar subquery returns zero rows?
The result is NULL. This does not cause an error.
What if a scalar subquery returns multiple rows?
PostgreSQL raises an error: 'more than one row returned by a subquery used as an expression'. Use an aggregate or LIMIT 1 to prevent this.