Scalar Subqueries

A subquery returning one row and one column can stand wherever a value can; two rows raise ERROR 1242 at run time.

SyntaxSELECT col, (SELECT expr FROM t2 WHERE t2.id = t1.id) AS alias FROM t1;

A scalar subquery is a SELECT in parentheses that produces exactly one row and one column. The MySQL 8.4 manual calls it a simple operand: you can use it almost anywhere a single column value or literal is legal — in the select list, in WHERE, in ORDER BY, as a function argument. It lets a query use a value it has to look up rather than one you can type.

Cardinality is checked while running

MySQL does not verify the row count when it parses the statement. It checks as the query executes, and there are three outcomes:

  • No rows — the subquery is NULL. The manual is explicit that nullability is not copied from the source column, because an empty result is always NULL.
  • One row — you get the value.
  • Two or more rows — ERROR 1242 (21000): Subquery returns more than 1 row.

Selecting two columns instead gives ERROR 1241 (21000): Operand should contain 1 column(s).

Shorthand and longhand

To compare several columns against one lookup row, you can write separate scalar subqueries or a single row subquery. They mean the same thing:

WHERE a = (SELECT x FROM t) AND b = (SELECT y FROM t)  -- longhand: two subqueries
WHERE (a, b) = (SELECT x, y FROM t)                    -- row subquery: one

(a, b) and ROW(a, b) are the same row constructor. Likewise (TABLE t2) is shorthand for (SELECT * FROM t2).

Where it will not go

Any context that demands a literal rejects a subquery. LIMIT requires literal integers, so LIMIT (SELECT ...) will not parse.

Example

Example · sql
-- Order lines shown against the product's current catalogue price,
-- and against the average line price of the same order.
SELECT
    oi.order_id,
    oi.product_id,
    oi.unit_price,
    -- products.id is the primary key, so this can match at most one row
    (SELECT p.price
       FROM products AS p
      WHERE p.id = oi.product_id) AS current_price,
    -- an aggregate always returns exactly one row, so it is safe as a scalar
    oi.unit_price - (SELECT AVG(oi2.unit_price)
                       FROM order_items AS oi2
                      WHERE oi2.order_id = oi.order_id) AS diff_from_order_avg
FROM order_items AS oi
WHERE oi.order_id = 4711;

When to use it

  • Show each order line next to the product's current catalogue price.
  • Filter rows against an aggregate of the whole table, such as the average order total.
  • Pull one column from the most recent related row into a report without adding a join.
  • Compare two columns at once against a single lookup row using a row subquery.

More examples

Uncorrelated scalar subquery as a computed constant

The threshold is data, not a constant you can hard-code, and it must not drift as the query runs. Reach for this whenever a filter compares a row to a summary of the same table: 'above average', 'newer than the latest import', 'more than the median price'. Because the subquery has no outer reference it is uncorrelated, which is the cheapest form.

Example · sql
-- The inner query never mentions the outer row, so it does not depend on it
-- and the optimizer can evaluate it once for the whole statement.
SELECT o.id, o.customer_id, o.total
FROM orders AS o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE status = 'paid')
ORDER BY o.total DESC;

Two scalar subqueries versus one row subquery

Row subqueries accept =, >, <, >=, <=, <>, != and <=>, and require at least two columns on each side. Use the row form when the values must come from the same row of the lookup table: it says that directly and fetches the customer once, while the longhand repeats the same lookup twice and can drift out of sync when someone edits only one of them. Neither form can silently mix two customers, though: if the email is not unique, both spellings fail at run time with ERROR 1242 (21000): Subquery returns more than 1 row, because a scalar subquery and a row subquery may each return at most one row.

Example · sql
-- Longhand: two scalar subqueries, each looking the customer up separately.
SELECT *
FROM orders AS o
WHERE o.customer_id      = (SELECT c.id      FROM customers AS c WHERE c.email = '[email protected]')
  AND o.shipping_country = (SELECT c.country FROM customers AS c WHERE c.email = '[email protected]');

-- Shorthand: one row subquery. (a, b) and ROW(a, b) are the same constructor,
-- and the customer row is fetched once.
SELECT *
FROM orders AS o
WHERE (o.customer_id, o.shipping_country)
      = (SELECT c.id, c.country FROM customers AS c WHERE c.email = '[email protected]');

Fixing ERROR 1242

This is the failure mode you will actually hit in production. LIMIT is allowed inside a scalar subquery (the restriction on LIMIT applies only to subqueries used with IN, ALL, ANY and SOME), so ORDER BY plus LIMIT 1 is the normal fix when the relationship is one-to-many. Always include a tiebreaking column, or two rows with the same timestamp make the result non-deterministic.

Example · sql
-- Works on seed data, then fails with
-- ERROR 1242 (21000): Subquery returns more than 1 row
-- as soon as any customer has a second order.
SELECT c.id,
       (SELECT o.id FROM orders AS o WHERE o.customer_id = c.id) AS last_order_id
FROM customers AS c;

-- Fixed: make the subquery deterministic and single-row.
SELECT c.id,
       (SELECT o.id
          FROM orders AS o
         WHERE o.customer_id = c.id
         ORDER BY o.ordered_at DESC, o.id DESC  -- tiebreak, or the pick is arbitrary
         LIMIT 1) AS last_order_id
FROM customers AS c;

Related

sql correlatedsql derived tablessql subquery vs joinsql nullsql sum avg

Discussion

  • Be the first to comment on this lesson.