Subqueries Returning Single Values
What you'll learn: How to use subqueries that return a single value for comparisons in WHERE clauses.
What is a Scalar Subquery?
A scalar subquery is a subquery that returns exactly one value—a single row with a single column. Think of it like asking a question that has one specific answer, then using that answer in a comparison.
You already know how to use subqueries in WHERE clauses. Now you'll learn to use them with comparison operators (=, >, <, >=, <=) when the subquery returns just one value.
How It Works
Imagine you want to find all products priced above the average. You can't just write WHERE price > AVG(price) because aggregate functions don't work directly in WHERE clauses. Instead, you use a subquery:
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
The subquery (SELECT AVG(price) FROM products) calculates one number—let's say 50.00—and the outer query becomes WHERE price > 50.00.
Common Patterns
Finding records above/below an aggregate:
WHERE salary > (SELECT AVG(salary) FROM employees)
Comparing to a specific record:
WHERE order_date > (SELECT hire_date FROM employees WHERE employee_id = 100)
Equality checks:
WHERE department_id = (SELECT department_id FROM employees WHERE name = 'John')
The key requirement: your subquery must return exactly one value. If it returns zero rows or multiple values, you'll get an error or unexpected behavior.
Key Takeaway: Scalar subqueries return a single value that you can use with comparison operators (=, >, <, >=, <=) in WHERE clauses, enabling dynamic filtering based on calculated or looked-up values.