What is a Scalar Subquery?
A scalar subquery returns ONE value (one row, one column).
Simple rule: Inner query = one number, Outer query uses it
How Scalar Subquery Works
Basic Syntax
SELECT columns
FROM table
WHERE column > (SELECT AGG(column) FROM table);Example 1: Find Above Average
SELECT name, age
FROM students
WHERE age > (SELECT AVG(age) FROM students);Step 1: Inner query → AVG(age) = 21 Step 2: Outer query → WHERE age > 21
Example 2: Find the Oldest
SELECT name, age
FROM students
WHERE age = (SELECT MAX(age) FROM students);Scalar Subquery Rules
- Must return ONE value - one row, one column
- Used with - =, >, <, >=, <=, !=
- Common functions - AVG, MAX, MIN, COUNT, SUM
Try It Below
Practice finding above-average students!
What Comes Next
Next: Column Subqueries - return a list of values.
Find Above Average
AVG(age) = 21. Who is older than 21? Try: SELECT * FROM students WHERE age = 22;
Source Table: students
| id | name | age |
|---|---|---|
| 1 | John | 20 |
| 2 | Mary | 22 |
| 3 | Peter | 21 |
| 4 | Sarah | 23 |
4 rows
SQL Editor
Loading...
Find the Oldest
MAX(age) = 23. Who has age = 23? Try: SELECT name FROM students WHERE age = 23;
Source Table: students
| id | name | age |
|---|---|---|
| 1 | John | 20 |
| 2 | Mary | 22 |
| 3 | Peter | 21 |
| 4 | Sarah | 23 |
4 rows
SQL Editor
Loading...