SQL / SUBQUERIES AND CTES
Scalar subqueries that return a single value
Use a one-row, one-column subquery as a value in SELECT, WHERE and UPDATE, and predict what happens when it matches nothing or too much.
What you will learn
- Drop a one-row, one-column subquery anywhere a single value is allowed
- Force the one-row shape with an aggregate or a unique-key filter
- Read a subquery that matches no row as NULL, which makes comparisons unknown
- Spot "more than one row returned" as a data bug, not a typo
Understanding Scalar subqueries that return a single value
A scalar subquery is a SELECT in parentheses that the surrounding statement treats as a single value: one row, one column. Because it is an expression rather than a row source, it goes wherever a literal or a column reference could go - in the select list, on either side of a WHERE comparison, in HAVING, in ORDER BY, in SET price = (...) of an UPDATE. (SELECT MAX(price) FROM products) is legal in all of those places for exactly the same reason the number 320 is.
That one-row promise is checked while the statement runs, against the actual data, not when it is parsed. If the inner query finds nothing, the expression is NULL, and NULL in a comparison gives unknown, so price > (SELECT price FROM products WHERE name = 'Chair') quietly matches no rows instead of complaining. If it finds two or more rows there is no single value to hand back, and Postgres aborts with "more than one row returned by a subquery used as an expression". An aggregate with no GROUP BY is the dependable guarantee, because MAX, SUM and COUNT return exactly one row even over an empty table.
The mental model that keeps this straight is substitution. A scalar subquery that mentions nothing from the outer row can be evaluated once and its result pasted in as a constant, which is why top_price repeats identically on every output row and does not cost one extra scan per row. It is also not a join: pulling a single number in this way cannot duplicate or drop outer rows, which is what makes it the right tool for comparing each row against one summary figure.
CREATE TABLE products (
id integer PRIMARY KEY,
name text UNIQUE,
price integer
);
INSERT INTO products (id, name, price) VALUES
(1, 'Keyboard', 45),
(2, 'Monitor', 180),
(3, 'Mouse', 25),
(4, 'Desk', 320);
-- one value out of a whole table, used as a column and as a threshold
SELECT name,
price,
(SELECT MAX(price) FROM products) AS top_price
FROM products
WHERE price > (SELECT price FROM products WHERE name = 'Mouse')
ORDER BY price;A subquery that returns exactly one row and one column stops being a query and becomes a value, and the engine enforces that shape at run time.
Worked examples
Zero rows means NULL
A scalar subquery that matches nothing is not an error and is not zero: it is NULL, which silently empties the outer result.
-- 'Chair' is not in the table, so the inner query returns zero rows
SELECT (SELECT price FROM products WHERE name = 'Chair') AS chair_price,
(SELECT COUNT(*) FROM products WHERE name = 'Chair') AS chair_rows;
SELECT COUNT(*) AS still_matching
FROM products
WHERE price > (SELECT price FROM products WHERE name = 'Chair');Example explained
Line 1The first subquery finds no row, so the expression is NULL; many clients print that as an empty cell rather than the word NULL.
Line 2COUNT(*) over the same empty filter still returns one row holding 0, which is why bare aggregates are always safe in a scalar slot.
Line 3In the second statement price > NULL is unknown for all four products, so WHERE keeps none of them and the count is 0.
Line 4No error is raised: zero rows is a legal scalar result, only two or more rows is not.
Two rows in a one-value slot
Shows the run-time failure when the inner query returns several rows, and the aggregate that repairs it.
-- fails: three prices cannot fit in one value slot
SELECT name FROM products
WHERE price > (SELECT price FROM products WHERE price > 40);
-- works: MIN collapses those three rows into one value
SELECT name FROM products
WHERE price > (SELECT MIN(price) FROM products WHERE price > 40)
ORDER BY price;Example explained
Line 1SELECT price FROM products WHERE price > 40 returns 45, 180 and 320, so the comparison has three candidate values and no way to choose.
Line 2Postgres and MySQL abort the whole statement; the error appears only when such data exists, so the same SQL may have worked yesterday.
Line 3MIN(price) with no GROUP BY reduces those three rows to the single value 45, making the shape guaranteed rather than lucky.
Line 4The fixed query keeps products priced above 45, which is Monitor at 180 and Desk at 320.
One total reused on every row
Uses a scalar subquery as a constant inside arithmetic to express each price as a share of the total.
SELECT name,
price,
ROUND(price * 100.0 / (SELECT SUM(price) FROM products), 1) AS pct_of_total
FROM products
ORDER BY price DESC;Example explained
Line 1SUM(price) has no GROUP BY, so it always produces exactly one row - here 570 - which is what a scalar position requires.
Line 2The subquery references nothing from the outer row, so the engine computes 570 once instead of once per product.
Line 3Writing 100.0 forces numeric division; price * 100 / 570 with integers would truncate Keyboard's share to 7.
Line 4The four percentages add up to 100.0, confirming every row was divided by the same total.
Important notes
Engines disagree on the too-many-rows case: Postgres and MySQL raise an error, Oracle raises ORA-01427, but SQLite quietly keeps the first row it reads, so a query that looks fine there can fail on the server.
Adding LIMIT 1 makes any subquery scalar, but only ORDER BY together with LIMIT 1 picks a defined row; using LIMIT 1 purely to silence the error hides which value you actually got.
Common mistakes
Looking up on a non-unique column, as in (SELECT price FROM products WHERE category = 'input'): correct while one row matches, then the statement dies with "more than one row returned by a subquery used as an expression" the day a second row is inserted.
Treating a missing lookup as 0, so WHERE stock < (SELECT reorder_level FROM thresholds WHERE sku = 'X') returns no rows at all when that sku is absent - the comparison is unknown, not false, and nothing tells you why the report is empty.
Putting two columns inside the parentheses, such as (SELECT name, price FROM products WHERE id = 1), which is rejected with "subquery must return only one column" because a scalar slot takes one column, not a whole row.
Try it yourself
Change, predict, then run
Create the four-row products table, then write one query returning each product's name, its price, and the gap between its price and the cheapest price using a scalar subquery. Now delete MIN from that subquery, re-run it, and read the error the engine gives you.
Open the SQL workspaceCheck your understanding
A products table has no row with name = 'Chair'. What does SELECT name FROM products WHERE price > (SELECT price FROM products WHERE name = 'Chair'); return?
- An error, because a scalar subquery must find a row
- Every product, because an unknown comparison is skipped rather than applied
- No rows, because the subquery is NULL and price > NULL is never true
- Only products priced above 0, because the missing price is treated as 0
Show answer
A scalar subquery that matches nothing evaluates to NULL, so price > NULL is unknown for every row and WHERE keeps only rows that are true, leaving an empty result. Option 0 is tempting because too many rows really is a run-time error, but too few rows is perfectly legal and produces NULL instead.