What is a Scalar Subquery?
Students Table
| id | name | age |
|---|---|---|
| 1 | John | 18 |
| 2 | Mary | 22 |
| 3 | Peter | 20 |
3 rows
A scalar subquery is a query inside another query that returns ONE single value (one row, one column).
Simple analogy: Like asking "What is the average price?" and getting one number: 50.
Basic Example
Students Table
| id | name | age |
|---|---|---|
| 1 | John | 18 |
| 2 | Mary | 22 |
| 3 | Peter | 20 |
3 rows
Find students older than the average age:
SELECT name, age
FROM students
WHERE age > (SELECT AVG(age) FROM students);How it works:
- Inner query calculates average: 20
- Outer query finds students where age > 20
Real Example
Students Table
| id | name | age |
|---|---|---|
| 1 | John | 18 |
| 2 | Mary | 22 |
| 3 | Peter | 20 |
3 rows
Find products more expensive than average:
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);Another Example
Students Table
| id | name | age |
|---|---|---|
| 1 | John | 18 |
| 2 | Mary | 22 |
| 3 | Peter | 20 |
3 rows
Find the oldest student:
SELECT name, age
FROM students
WHERE age = (SELECT MAX(age) FROM students);Simple! Inner query finds max age, outer query finds who has that age.
Summary
Students Table
| id | name | age |
|---|---|---|
| 1 | John | 18 |
| 2 | Mary | 22 |
| 3 | Peter | 20 |
3 rows
Scalar subquery = Returns ONE value Use in WHERE clause to compare with calculated values