How Do Subqueries Work in SQL?

A subquery is a SELECT statement nested inside another statement. There are three kinds, distinguished by what they return and where they can appear.

A scalar subquery returns a single value — one row, one column — and can be used anywhere a single value is allowed: in the SELECT list, in a WHERE comparison, in a GROUP BY or ORDER BY, or inside another expression. A table subquery returns a set of rows and can appear in the FROM clause, where it is called a derived table, or on the right-hand side of IN, EXISTS, ANY and ALL. A correlated subquery is one that refers to a column from the enclosing query, which makes it dependent on the outer row.

There are also set operations that behave like subqueries in some ways — IN, BETWEEN, ANY, ALL — but they are operators rather than nested SELECT statements.

Two rules to keep straight. A subquery in the FROM clause is a derived table and needs an alias, because the outer query refers to it by that alias. And a subquery cannot contain the clause it is nested inside at the same level: a SELECT list subquery cannot itself have a SELECT list subquery that references the outer-most query in an unsupported way, and a WHERE clause cannot contain a subquery that returns a set unless the operator expects one.

The practical advice is to start with the simplest form that expresses the requirement. A filter usually wants IN or EXISTS; a value in the SELECT list wants a scalar subquery; a shape change wants a derived table. Reach for the more elaborate forms only when the simple one cannot say what you mean.

When the list of acceptable values comes from another table, a subquery in the WHERE clause is the natural expression of the rule.

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name VARCHAR(60) NOT NULL
);

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  category_id INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL
);

INSERT INTO categories (id, name) VALUES
  (1, 'peripherals'),
  (2, 'displays'),
  (3, 'furniture');

INSERT INTO products (id, name, category_id, unit_price) VALUES
  (1, 'Mechanical Keyboard', 1, 79.50),
  (2, 'USB-C Hub',        1, 32.00),
  (3, '27-inch Monitor',  2, 249.99),
  (4, 'Standing Desk',    3, 419.00);

SELECT id, name, unit_price
FROM products
WHERE category_id IN (
  SELECT id
  FROM categories
  WHERE name = 'peripherals'
)
ORDER BY unit_price DESC;

Two rows come back: the Mechanical Keyboard at 79.50 and the USB-C Hub at 32.00.

The advantage over hard-coding the ids is that the rule lives in one place. If the category is renamed, or the ids change, only the subquery needs updating — and a query that reads WHERE category_id IN (1) tells the reader nothing about why product 1 is in the result.

Two practical points. First, IN is the right operator here rather than EXISTS, because the subquery is a plain list of ids with no correlation to the outer row; EXISTS would need a join condition to be meaningful, and EXISTS (SELECT 1 FROM categories) would return true for every product because the subquery is not correlated at all. Second, an empty subquery result is safe with IN — it simply matches nothing, so the query returns zero rows. That is not true of NOT IN, which is where the NULL trap lives.

If you want the category name in the output rather than just filtering by it, a join is the better tool, since the subquery version would need the id joined back to the categories table anyway. Use the form that matches what the report has to show.

A scalar subquery in the SELECT list computes one value for each output row, without changing the row count or collapsing the rows into groups.

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 ROUND(SUM(o.amount), 2)
        FROM orders AS o
        WHERE o.customer_id = c.id) AS total_spend
FROM customers AS c
ORDER BY c.id;

Three rows come back: Ada Lovelace with 460.50, Grace Hopper with 75.25 and Alan Turing with NULL. Alan has no orders, so the SUM inside the subquery has nothing to add up and returns NULL.

Note that this subquery is correlated — it refers to c.id from the enclosing query — so the engine evaluates it once per customer. With three customers that is three executions. On a table with a million customers and a large orders table, that is a million separate lookups, and the query becomes slow even though each individual lookup is fast and indexed. The fix is to pre-aggregate: compute the sums per customer in a derived table and join that to the customers, so the work happens once per order rather than once per customer.

That trade-off is the general shape of subqueries in the SELECT list. They are very readable for a small table or a one-off exploration, and they are the wrong tool for a large one. The readability is real — the value sits right next to the columns it explains — but the row-multiplying behaviour of a GROUP BY is something to be careful with as well: if you add a GROUP BY to the outer query to collapse rows, the correlated subquery is evaluated per group rather than per row, which changes both the results and the cost.