Scalar vs Table Subqueries

A scalar subquery returns exactly one value: one row, one column. It can be used anywhere a single value is allowed — in the SELECT list, compared in a WHERE clause, inside an arithmetic expression, or in a GROUP BY. A table subquery returns a set of rows and can only be used where a set is expected: in the FROM clause as a derived table, or on the right-hand side of IN, EXISTS, ANY or ALL.

The distinction is enforced by the engine. If a scalar subquery returns more than one row, the query fails with an error — in SQLite you get "subquery returns more than one row", in PostgreSQL "more than one row returned by a subquery used as an expression". If a scalar subquery returns no rows, the result is NULL, and the error does not occur. That asymmetry is worth remembering: too many rows is fatal, too few is fine.

Table subqueries have the opposite flexibility. A derived table in the FROM clause needs an alias and behaves like a table: you can select from it, join to it, group by its columns and reference its aliases in the outer query. An IN subquery takes any number of rows and simply matches against them.

There is also an intermediate form: a row subquery that returns one row with several columns, written as (SELECT a, b FROM t WHERE ...). This is legal in a FROM clause and in a comparison against a row constructor, and it is the shape to reach for when you want to compare two columns together rather than one.

Choosing between them is mostly a question of what the value means. If the answer is a single number — this customer's total, the maximum date, a count — it is scalar. If the answer is a set — these are the ids that qualify — it is a table subquery. If it is a set of columns that you want to treat like a table, it is a derived table.

Here is the same thing as runnable code:

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- A scalar subquery: exactly one value.
SELECT ROUND(AVG(amount), 2) AS average_order FROM orders;

-- A table subquery in the FROM clause: a derived table with an alias.
SELECT t.customer_id, t.order_count
FROM (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
) AS t
WHERE t.order_count > 1
ORDER BY t.customer_id;

The query fails. The engine expects a single value and refuses to guess which row you meant, so it raises an error rather than silently picking one. In SQLite the message is "subquery returns more than one row"; in PostgreSQL it is "more than one row returned by a subquery used as an expression". MySQL reports error 1242, "Subquery returns more than 1 row".

The good news is that the error is loud. It happens at execution time rather than compile time, which means a query that looks fine in review can still fail in production the first time the data changes — the classic case is a "latest order per customer" subquery that is correct while every customer has exactly one order, and breaks the moment a customer places a second one.

There are three ways to make it safe, and the choice depends on which one you actually mean. If you want the largest or smallest, wrap the subquery in an aggregate: (SELECT MAX(o.amount) FROM orders o WHERE o.customer_id = c.id). If you want one arbitrary row, use ORDER BY plus LIMIT 1 — but then be honest with yourself, because the ordering makes the choice deterministic rather than meaningful, and two rows with the same sort key still leave the result up to the engine. If you want the most recent row per customer, use ORDER BY on a timestamp plus a tiebreaker on the primary key, and LIMIT 1 inside the subquery, or use a window function with ROW_NUMBER, which handles ties explicitly.

There is a fourth option worth knowing, and it is often the best one: replace the correlated subquery with a window function. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) = 1 inside a derived table expresses "the latest order for each customer" with no error condition and no arbitrary tie-breaking, and it computes in one pass instead of once per customer.

A scalar subquery in the SELECT list, with an aggregate inside it so that it can only ever return one value per customer.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

SELECT c.id,
       c.name,
       (SELECT MAX(o.amount)
        FROM orders AS o
        WHERE o.customer_id = c.id) AS largest_order
FROM customers AS c
ORDER BY c.id;

Three rows come back: Ada Lovelace with 340.50, Grace Hopper with 75.25 and Alan Turing with NULL. Alan has no orders, and MAX over an empty set is NULL rather than an error.

The MAX inside the subquery is what makes it safe. Without it, the subquery (SELECT o.amount FROM orders o WHERE o.customer_id = c.id) would return one row for Ada and Grace and two for any customer with more than one order — and the query would fail with a "subquery returns more than one row" error for that customer rather than silently picking one.

The subquery is correlated, so it runs once per customer. With a small table that is fine. On a large one, pre-aggregating in a derived table and joining is faster, and it produces the same result:

the derived table groups the orders by customer_id and computes MAX(amount) per group, then the outer query joins that to customers with a LEFT JOIN so customers without orders still appear, with NULL for their largest order.

That version is also the more honest shape, because it separates two concerns: computing the per-customer maximum, and presenting it next to the customer. When the subquery version starts duplicating that expression across several output columns, the derived-table version is the one to switch to.