Scalar Subqueries in SELECT and WHERE
Single-value subqueries and the error when they return more than one row.
What an Interviewer Means by Scalar Subquery
A scalar subquery is a query that returns exactly one row and one column — a single value. Because it resolves to one value, SQL lets you drop it almost anywhere a literal could go: in SELECT, WHERE, HAVING, even ORDER BY.
- Interviewers test whether you know the one row, one column rule.
- The classic trap: a subquery that accidentally returns more than one row.
If you can state that definition cleanly, you have already passed the first checkpoint.
A Scalar Subquery in the SELECT List
Putting a scalar subquery in the SELECT list lets you attach a computed single value to every output row. Here we show each employee next to the company-wide average salary.
The subquery (SELECT AVG(salary) FROM employees) runs and collapses the whole table to one number, then that number is repeated on every row.
SELECT
name,
salary,
(SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;All lessons in this course
- Scalar Subqueries in SELECT and WHERE
- Subqueries in the FROM Clause (Derived Tables)
- IN, ANY and ALL Subqueries
- EXISTS vs IN Performance