Subqueries in SELECT, FROM, and WHERE

## What "subquery" means here A subquery is a complete `SELECT` statement enclosed in parentheses and written inside another statement; the enclosing statement is the *outer query*. There is no single rule for what a subquery may look like. The position you put it in decides what shape it must have, what it contributes to the result, and what names it can see. Interviewers ask about placement because it separates people who learned SQL as a grammar from people who learned it as a set of copied recipes. ## Position 1 — the SELECT list: a computed column In the select list a subquery stands where a column expression stands. It contributes exactly one column, and for each row the outer query emits it must produce at most one value: one column, at most one row. If it returns no row at all, the column is NULL for that row. ```sql SELECT c.name, (SELECT COUNT(*) FROM orders) AS orders_in_system FROM customers c; ``` Because it is an expression, it can go anywhere an expression can — inside a `CASE`, inside arithmetic, as a function argument. What it cannot do is widen the result: a select-list subquery never adds rows or columns beyond the one it occupies. ## Position 2 — the FROM clause: a derived table In `FROM`, a subquery stands where a table reference stands, and the result is called a *derived table* (some engines say *inline view*). It may return any number of rows and columns, and the outer query treats it exactly like a table: join it, filter it, group it, sort by it. ```sql SELECT big.customer_id, big.total FROM (SELECT customer_id, total FROM orders WHERE total > 100) AS big; ``` Two rules travel with this position. First, the derived table must be given a name — the standard requires a correlation name, and PostgreSQL and MySQL both reject the query without one. Second, it is scoped independently: a plain derived table cannot reference columns belonging to another item in the same `FROM` list, or to an enclosing query, unless the `LATERAL` keyword is written in front of it. ## Position 3 — WHERE and HAVING: a predicate operand In `WHERE` the subquery is one side of a predicate, and there are two families. A comparison predicate (`=`, `<`, `>=`) needs a single value on the subquery side. A membership or quantified predicate (`IN`, `EXISTS`, `ANY`, `ALL`) accepts a whole result set. ```sql SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); ``` `HAVING` is the same idea moved past grouping. Because `HAVING` filters groups rather than rows, it is the only filter position where a subquery result can be compared against an aggregate of the group: ```sql SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id HAVING SUM(total) > (SELECT AVG(total) FROM orders); ``` ## Other legal positions A scalar subquery is a value expression, so it is also permitted as an `ORDER BY` sort key and inside a `CASE` branch, though both are rare and usually less readable than computing the value once and naming it. Subqueries appear in `INSERT`, `UPDATE` and `DELETE` statements too, where the same positional logic applies: a single-value subquery on the right of an assignment, a set-producing subquery as the source of rows or as a filter. ## Choosing a position The practical rule of thumb: if the subquery produces one annotation value that belongs next to a column, the select list reads best. If it produces a set of rows the rest of the query needs to join, filter or sort on, put it in `FROM` and name it. If it only decides whether a row survives, put it in `WHERE` as a predicate operand and let the predicate do the work. ## What goes wrong The three classic failures map exactly onto the three positions. A select-list subquery that can return more than one row raises a runtime error the moment a second row appears. A derived table without an alias fails to parse on the strict engines. And a filter written in `WHERE` that needs an aggregate of the group belongs in `HAVING` instead — `WHERE` runs before grouping and has no group to talk about.