SQL Single-Row Subquery
Guaranteed to return one row — so it can safely sit next to =, >, or <.
What & Why
A single-row subquery returns exactly one row (though possibly several columns). Because there's no ambiguity about which row to use, it can be compared directly with =, >, <, and similar operators.
See How It Works
BUSINESS QUESTION
Product wants feedback ratings above the overall average rating.
| id | user_id | feature_id | rating | nps_score | comment | created_at |
|---|---|---|---|---|---|---|
| 1 | 101 | 1 | 5 | 9 | Clear and useful | 2024-01-25 12:15:00+00 |
| 2 | 102 | 2 | 4 | NULL | Export worked well | 2024-02-20 15:40:00+00 |
| 3 | 103 | 1 | 4 | 7 | Helpful setup flow | 2024-03-18 09:05:00+00 |
| 4 | 104 | 3 | 3 | NULL | Needs clearer timing | 2024-04-08 17:30:00+00 |
EXAMPLE QUERY
SELECT
id AS feedback_id,
rating
FROM product.feedback
WHERE rating > (SELECT AVG(rating) FROM product.feedback)
ORDER BY rating DESC, feedback_id;RESULT — ratings above the 4.00 average
| feedback_id | rating |
|---|---|
| 1 | 5 |
The subquery returns one average value, and only feedback 1 exceeds it.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario