Why must a derived table in the FROM clause be given an alias?

## The derived table is a table When you write a parenthesised `SELECT` in the `FROM` clause, the result is a *derived table* — a table that exists only for the duration of this statement. The key mental move is that the outer query does not see a subquery there at all: it sees a table, with rows and columns, sitting in the FROM list next to whatever other tables you listed. Everything that is true of a real table reference is therefore true of it, and the first of those things is that it needs a name. ```sql -- Rejected on the strict engines: the derived table has no name SELECT * FROM (SELECT id, total FROM orders WHERE total > 100); -- Valid SELECT big.id, big.total FROM (SELECT id, total FROM orders WHERE total > 100) AS big; ``` ## Why the name is needed at all Three jobs depend on it. **Qualification.** `big.total` is only writable because `big` exists. Without a name there is no prefix you could use. **Disambiguation.** As soon as the derived table joins something else that has an `id` column, the parser needs to know which `id` you mean. Table names resolve that; a nameless table cannot. **Self-joins and repetition.** If the same derived table appears twice in one FROM list, only distinct correlation names keep the two apart. The SQL standard therefore mandates a correlation name on a derived table. PostgreSQL enforces it with `ERROR: subquery in FROM must have an alias`, and MySQL with `Every derived table must have its own alias`. Some engines are lenient and accept the omission, which is exactly why relying on it is a portability trap: the query is correct nowhere in the standard and happens to work in one place. ## The optional column list The alias can rename the columns too. Standard SQL, and PostgreSQL among others, accept a column list after the correlation name: ```sql SELECT d.customer, d.spend FROM (SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id) AS d(customer, spend); ``` The list must have one entry per output column of the subquery. This form is handy when the inner query is generated or otherwise inconvenient to edit, because it fixes the outward-facing names without touching the body. ## Name your expressions inside The alias names the *table*, not its columns. A column produced by an expression has no natural name, and what an engine invents for it is implementation-dependent — PostgreSQL will show something like `?column?`, others pick something else. Any outer reference to such a column is therefore unreliable unless you name it yourself: ```sql SELECT line.order_id, line.line_total FROM ( SELECT order_id, price * quantity AS line_total FROM order_items ) AS line WHERE line.line_total > 50; ``` ## What the alias hides A point candidates trip over: once the subquery is named, the tables *inside* it are invisible from outside. In `FROM (SELECT id FROM orders) AS d`, the outer query may write `d.id` but not `orders.id` — the name `orders` does not exist at the outer level, and referencing it either fails or, worse, silently resolves to a different `orders` you also listed. The derived table is a boundary: what crosses it is exactly the columns of its select list, under the names that select list gave them. ## `AS` is optional; the name is not In standard SQL and in the major engines, the `AS` keyword before a table alias may be omitted: `FROM (SELECT …) d` and `FROM (SELECT …) AS d` mean the same thing. Many teams require `AS` anyway because it makes the alias visible at a glance and prevents the classic missing-comma bug in a select list, where a forgotten comma turns the next column name into an alias for the previous expression. The keyword is style; the name itself is a rule.