Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT title, price
FROM books
WHERE price > (SELECT AVG(price) FROM books);

Try it

SQL Books above the average price ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Empty results become NULL ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL More than one row: SQLite is lenient, others fail ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
(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.
SQL
-- 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.
  • NULL on empty. Comparing to an empty scalar subquery gives NULL, so WHERE 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 returns 0 rather than NULL when 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 BY or 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 ...) sets NULL when the subquery finds nothing, which is a classic source of lost data.