--- title: "07 — Subqueries: A Query Inside a Query" uid: subqueries tags: ["sql", "roadmap:sql", "subqueries", "expressions", "scalar", "correlated", "nested"] excerpt: "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." date: 2026-08-13T03:27:37+0000 source: https://www.aveshina.my.id/en/blog/subqueries --- 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. ```figure ``` ## 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](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](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/](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](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](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](https://www.youtube.com/watch?v=TUxadt94L0M) ```quiz Q: A subquery placed in WHERE total > (...) must return… - one column and one row (a scalar) - any number of columns and rows correct: 0 explain: A comparison operator like > needs a single value to compare against, so the subquery must be scalar — one column, one row. Q: What makes a subquery "correlated"? - It returns a table for the FROM clause - It references a column from the outer query, so it's re-evaluated per outer row correct: 1 explain: Correlation means the inner query depends on the outer row. Because of that dependency it can't run standalone and must execute once per outer row. Q: A non-correlated (nested) subquery is cheap because… - it never references the outer query, so the engine evaluates it once and reuses the result - it always returns zero rows correct: 0 explain: Independence from the outer row lets the engine run the inner query a single time and feed the same result to every outer row. Q: Which slot accepts a subquery returning many rows and one column? - WHERE col IN (...) - WHERE total > (...) correct: 0 explain: IN expects a set of values in one column. A scalar comparison slot (>) requires exactly one row. Q: On a large table, a correlated subquery computing per-customer totals is usually slower than… - a JOIN against a grouped subquery, because the grouping happens once instead of per row - the same correlated subquery with more columns correct: 0 explain: Rewriting the per-row correlated logic as a single grouped JOIN lets the engine aggregate once, which is dramatically cheaper at scale. ```