SQL Reference ยท Joins and Subqueries
SQL scalar subqueries
Reference for scalar subqueries in SQL: a query that returns one value, used in SELECT, WHERE and SET, with NULL, empty-result and multi-row behaviour.
A scalar subquery is a SELECT in parentheses that returns exactly one column and at most one row. Wherever SQL expects a single value, you can put one: in the select list, in a WHERE comparison, in SET of an UPDATE, or inside an expression.
The most common use is comparing each row with an aggregate of the whole table, such as "books costing more than the average price". The subquery runs, produces one number, and the outer query uses it like a literal.
SELECT title, price FROM books WHERE price > (SELECT AVG(price) FROM books);
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
(SELECT single_column FROM ... [WHERE ...])
| Situation | Result |
|---|---|
| Subquery returns one row | That value. |
| Subquery returns no rows | NULL. |
| Subquery returns more than one row | An error in the standard and in PostgreSQL, MySQL, SQL Server and Oracle. SQLite takes the first row. |
| Subquery returns more than one column | An error (except in row-value comparisons, see below). |
Where allowed: select list, WHERE, HAVING, ORDER BY, SET in UPDATE, VALUES of INSERT, and function arguments.
Dialect differences
| Database | Behaviour with several rows |
|---|---|
| SQLite | No error; uses the first row, in an unspecified order unless you add ORDER BY and LIMIT 1. |
| PostgreSQL | Error: more than one row returned by a subquery used as an expression. |
| MySQL | Error 1242: Subquery returns more than 1 row. |
| SQL Server | Error 512: Subquery returned more than 1 value. |
| Oracle | Error ORA-01427: single-row subquery returns more than one row. |
-- Portable way to guarantee one row SELECT (SELECT MAX(price) FROM books WHERE genre = 'Fiction');
Use an aggregate such as MAX() or MIN(), or ORDER BY ... LIMIT 1 (TOP 1 in SQL Server, FETCH FIRST 1 ROW ONLY in Oracle 12c+) to force a single row.
Usage notes
- Uncorrelated or correlated. A subquery that does not reference the outer query runs once. If it references outer columns it is correlated and conceptually runs per row.
NULLon empty. Comparing to an empty scalar subquery givesNULL, soWHERE price > (SELECT ... no rows)returns nothing. Wrap with COALESCE() if you want a default.- Counting never yields NULL.
COUNT(*)always returns one row, so it returns0rather thanNULLwhen nothing matches. - Select-list subqueries and performance. A correlated scalar subquery in the select list can run once per output row. A join with
GROUP BYor a window function is often faster on big tables. - Row values. Some databases allow a subquery returning several columns in a row-value comparison:
(a, b) = (SELECT x, y FROM ...). SQLite supports this since 3.15; SQL Server does not. - Aliases. Give the subquery a column alias in the select list, as in the first example, so the result column has a readable name.
- Updates.
UPDATE t SET col = (SELECT ... WHERE ...)setsNULLwhen the subquery finds nothing, which is a classic source of lost data.