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.