Backend Development › Relational Databases & SQL

Subquery

A query nested inside another query.

Also known as: nested query, subselect, inner query, sub-select

A subquery is a SELECT inside another query. The inner query produces a value, a list of values or a whole table that the outer query uses.

Where they appear

In WHERE, to filter by the result of another query:

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_cents > 100000);

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);   -- has at least one order

In FROM, as a temporary table (a “derived table”, which needs an alias):

SELECT region, AVG(spent)
FROM (SELECT region, customer_id, SUM(total_cents) AS spent
      FROM orders GROUP BY region, customer_id) AS per_customer
GROUP BY region;

In SELECT, as a single value (a scalar subquery):

SELECT name, (SELECT MAX(total_cents) FROM orders o WHERE o.customer_id = c.id) AS biggest_order
FROM customers c;

A scalar subquery must return one row and one column, or you get an error (or arbitrary results in lenient databases).

Correlated vs uncorrelated

An uncorrelated subquery doesn’t reference the outer query and runs independently. A correlated subquery refers to the outer row (like the EXISTS example), so it’s conceptually evaluated per row. Optimizers can often rewrite it into a join, but not always.

Trap: NOT IN and NULL

If the subquery returns even one NULL, x NOT IN (subquery) is never true, so you get no rows. Prefer NOT EXISTS (NULL in SQL).

Subquery, join or CTE?

• Many subqueries can be written as a join, which is often clearer when you need columns from both tables.
• When a query nests several levels deep, a CTE names the steps and reads top to bottom.
• Use EXISTS / NOT EXISTS for “has or doesn’t have a matching row”.

Performance varies by database and query. Check the plan (query plan) instead of assuming one form is faster.