Last reviewed
Correct answer: D. A subquery that returns exactly one value
Explanation
A scalar subquery is a SELECT wrapped in parentheses that yields one row and one column, and the engine substitutes that result into the surrounding expression as if a literal sat there. PostgreSQL states the shape directly: "A scalar subquery is an ordinary SELECT query in parentheses that returns exactly one row with one column." MySQL calls it a scalar operand, usable almost anywhere a single column value or literal is legal.
The consequence shows up at runtime, and dialects differ. If the inner query returns two rows for some input, PostgreSQL raises an error rather than picking one; SQLite quietly takes the first row. A scalar subquery that can return two rows is a latent bug either way.
Option B describes a different construct: a subquery returning a whole table is a derived table in FROM, or the operand of IN, which is exactly what a scalar subquery may not be. Option C reverses reality: WHERE is where scalar subqueries most often live; the genuine restrictions sit where a literal is mandatory, such as LIMIT. Option D confuses shape with data type, which can be anything the column holds.
Sources
“A scalar subquery is an ordinary SELECT query in parentheses that returns exactly one row with one column.”
“In its simplest form, a subquery is a scalar subquery that returns a single value.”
“The value of a subquery expression is the first row of the result from the enclosed SELECT statement.”
Practise 3 questions on this topic
Take SQL Subqueries — Timed Test (3 questions) — scored instantly, explanation for every question, no login.