07 — Subqueries: A Query Inside a Query

Assembling subqueries by trial and error until they ran was my method, and it never explained why. Writing them down produced one model: a subquery is a query whose result feeds another query, and the slot it occupies (scalar, column, row, or table) decides what shape it has to return. [1][2]

The framing that clicked is shape-driven. A subquery isn't a different kind of query — it's a regular SELECT placed inside another statement, returning a value, a row, or a set of rows. Once I knew which slot I was filling, the shape requirement became obvious: a scalar slot needs one column and one row, a WHERE col IN (...) slot needs one column and many rows, a FROM slot needs a whole table. The error messages ("scalar subquery returned more than one row") stopped being cryptic.

Where a subquery can appear

A subquery is a SELECT embedded in another statement. It can sit in several slots, and each slot demands a specific return shape [1]:

• SELECT list — needs a scalar (one column, one row). Useful for attaching a computed benchmark to each output row.
• WHERE with =, >, etc. — needs a scalar too.
• WHERE col IN (...) — needs one column, many rows.
• FROM — needs a whole table (any columns/rows); this is the "derived table" or inline view.

-- scalar in WHERE: compare each row to the overall average
SELECT name, total FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- one-column set in WHERE: IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- table in FROM: a derived table
SELECT t.customer_id, t.spent
FROM (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) t
WHERE t.spent > 500;

Matching the subquery's shape to the slot is the whole skill. A scalar subquery that returns two rows in a WHERE total > (...) is a runtime error, not a logical one.

Nested subqueries — independent and run once

A nested (non-correlated) subquery doesn't refer to the outer query at all — it can run on its own, so the engine evaluates it once and feeds the result up [3][4]. The AVG(total) example above is nested: it computes one average over the whole orders table, independent of whichever outer row is being tested.

That independence is what makes nested subqueries cheap: one evaluation, reused for every outer row. If a subquery can be written without referencing the outer query, it should be.

Correlated subqueries — re-evaluated per row

A correlated subquery does reference the outer query's columns, so it cannot run standalone — the engine has to re-evaluate it once for each outer row [5][6].

SELECT c.name,
       (SELECT SUM(o.total) FROM orders o WHERE o.customer_id = c.id) AS spent
FROM customers c;

Here the inner query references c.id, so for each customer the engine runs the sum over that customer's orders. Correlated subqueries read clearly — "for each customer, their total spend" — but the per-row re-evaluation is the classic performance trap. On large tables, the same logic rewritten as a JOIN against a grouped subquery usually runs dramatically faster, because the grouping happens once instead of per row [5].

When subqueries, when joins

This is the decision I had to make peace with. Subqueries and joins often express the same logic; the question is which reads more clearly and which the optimizer handles better:

• Use a scalar subquery when I need one computed value plugged into a comparison or a SELECT list — the intent ("compare to the average") is obvious.
• Use a JOIN when I need columns from another table on every output row — joins are what the engine is built to optimize.
• Use a derived table in FROM when I need to aggregate first and filter the aggregate second, which is also what CTEs make readable (covered later).

Most "subquery vs join" debates collapse to this: prefer the join when performance matters at scale, prefer the subquery when it makes the logic clearer and the table isn't huge.

How I use this

The habit I keep is reading a subquery aloud and asking does the inner query mention the outer row? If yes, it's correlated, and the per-row cost is a flag — I'll check whether a grouped JOIN expresses the same thing once. If no, it's nested and cheap, and I leave it because it reads cleanly. And before writing any subquery I name the slot it's filling (scalar, IN-set, FROM-table); that single decision determines the shape requirement and catches the "returned more than one row" class of error at write time.

References

[1] TutorialsPoint, "SQL Sub Queries," tutorialspoint.com, 2024. [Online]. Available: https://www.tutorialspoint.com/sql/sql-sub-queries.htm

[2] YouTube, "Advanced SQL Tutorial | Subqueries," 2023. [Online]. Available: https://www.youtube.com/watch?v=m1KcNV-Zhmc

[3] StudySmarter, "Nested Subqueries in SQL," studysmarter.co.uk, 2024. [Online]. Available: https://www.studysmarter.co.uk/explanations/computer-science/databases/nested-subqueries-in-sql/

[4] YouTube, "MySQL Subqueries," 2023. [Online]. Available: https://www.youtube.com/watch?v=i5acg3Hvu6g

[5] MySQL, "Correlated Subqueries," dev.mysql.com, 2024. [Online]. Available: https://dev.mysql.com/doc/refman/8.4/en/correlated-subqueries.html

[6] YouTube, "Intro To Subqueries," 2023. [Online]. Available: https://www.youtube.com/watch?v=TUxadt94L0M