Scalar Subqueries in SQL
A scalar subquery is a SELECT statement embedded inside another query that returns exactly one column and one row, producing a single value. That value can be used anywhere a literal or column expression is valid.
A scalar subquery is a complete SELECT statement embedded inside another query that returns exactly one value — one row, one column.
You're building a product pricing report. You want to see each product's price alongside the overall average price, so you can tell at a glance which products are above the average and which are below. The average is a single number computed from the whole products table. The product details come from row-by-row reading. Both need to appear in the same result. A scalar subquery lets you compute that single number inside the query that reads the product rows.
The inner query runs first, produces one value, and that value slots into the outer query wherever a number or column reference would go.
The most common use: compare each row against a table-wide aggregate. Products priced above average:
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)The subquery computes the average across all products. The outer query uses that number as the right side of the comparison. SQL runs the subquery once and applies the result to every row.
You can put a scalar subquery in the SELECT list to add a computed column:
SELECT name, price, (SELECT AVG(price) FROM products) AS avg_price
FROM productsEvery row gets the same avg_price — the table-wide average — sitting next to the product's own price. One column from the table, one column from the subquery.
Scalar subqueries can also be correlated — referencing a value from the outer query:
The inner query re-runs for each outer row, using that row's category_id. Each product gets the average price for its own category — not the table-wide average. This is more expensive than a fixed scalar subquery because it runs once per row instead of once total.
The one thing that trips people up: a scalar subquery that returns more than one row causes an error.
A subquery like (SELECT price FROM products WHERE category_id = 3) might return dozens of rows. SQL can't place dozens of values in a single column slot and raises an error. Only queries that are guaranteed to return one row — typically ones using aggregate functions like AVG, MAX, MIN, or COUNT(*) — are safe as scalar subqueries.
A scalar subquery in WHERE returns 5 rows instead of 1. What happens?
9 Scalar Subqueries practice problems
Write a query to return the product name, its price, and the overall catalogue average price for every product.
Write a query to return the name and price of every product whose price exceeds the overall average.
Write a query to return the employee ID and salary amount for every above-average salary record.
Write a query to return the name and price of every product whose price equals the minimum price in the catalogue.
Write a query to return the order ID, total amount, and the overall order count for every order record.
Write a query to return the user ID, the conversion amount, and the combined total for every conversion record.
Write a query to return the count as a single number named `cheaper_than_max`.
Write a query to return the product name, price, and that product's own-category average price for every product.
Write a query to return the product name and price for every product that exceeds its own category's average price.
These problems are part of the Scalar Subqueries lesson in SQLMaxx, with instant grading and a worked solution on each.
Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.
That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.
Real problems. Not toy examples.
615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.
Write a query. Know if it's right in one second.
No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.
Stop guessing whether you actually know it.
SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.
Learn it once. Keep it for good.
Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.
Practice, feedback, mastery, review. That's the loop that turns reading into real skill.
Start freeNo account, no credit card. Start solving in under a minute.