Subquery in FROM

The Pattern: a Derived Table

A subquery placed in the FROM clause acts as a temporary, on-the-fly table — often called a derived table. The outer query can select from it, join it, filter it, exactly like any real table. The one non-negotiable rule: a derived table must be given an alias — most SQL engines will reject an un-aliased subquery in FROM with a syntax error, since every reference to a table needs a name to hang column references off (this is the same aliasing/renaming necessity introduced back in Chapter 7).

Sample data used throughout this chapter:

employees

departments

Example

sql
SELECT dept_totals.department, dept_totals.total
FROM (
  SELECT department, SUM(salary) AS total
  FROM employees
  GROUP BY department
) AS dept_totals
WHERE dept_totals.total > 100000;

The inner query first computes:

All three totals exceed 100000, so the outer filter passes all of them through unchanged here — every department qualifies.

Making the Filter Actually Exclude Something

Raise the bar to see the filtering do real work:

sql
SELECT dept_totals.department, dept_totals.total
FROM (
  SELECT department, SUM(salary) AS total
  FROM employees
  GROUP BY department
) AS dept_totals
WHERE dept_totals.total > 150000;

Now only Engineering (235000) and Sales (203000) pass; Marketing (132000) is filtered out.

Why the Alias Is Mandatory

Without AS dept_totals, the outer query has no name to prefix department and total with, and most engines simply refuse to parse the statement (ERROR: subquery in FROM must have an alias). This mirrors why Chapter 7 insists on aliasing joined/renamed columns and tables — SQL's binding rules need every row source to have an addressable name, whether it's a real table or a derived one.

Why Use a Derived Table At All?

• It lets you apply a WHERE filter to an aggregated result (HAVING does this too, but a derived table is more flexible for multi-step logic, chained aggregates, or reusing the same aggregate in multiple places).
• It can be joined to other real tables: ... FROM (subquery) AS dept_totals JOIN departments d ON d.name = dept_totals.department.
• It makes complex queries readable by breaking them into named, self-contained steps — similar in spirit to a CTE (WITH ... AS (...)), which many modern engines treat almost interchangeably with a FROM-subquery.

Edge Cases

• Column names inside the derived table must be unambiguous — an unaliased expression like SUM(salary) needs an alias (AS total) so the outer query has something to refer to.
• A derived table is (usually) materialized fresh each time the outer query runs — it is not a stored object; nothing persists after the query finishes.
• Nesting derived tables (a subquery in FROM whose own FROM contains another subquery) is legal but can quickly hurt readability — a CTE is often clearer for 2+ levels.

Key Takeaways / Q&A

Q: What is the one mandatory rule for a FROM-clause subquery? A: It must be aliased — an un-aliased derived table is a syntax error in most engines.

Q: How does this relate to Chapter 7's aliasing rules? A: Same underlying reason — SQL needs a name for every row source so columns can be unambiguously addressed; Chapter 7 covered this for renamed columns/joined tables, and a derived table is just another row source needing that name.

Q: Is a derived table the same as a CTE? A: Similar in purpose (both create a named, temporary result set), different syntax (FROM (subquery) AS alias vs WITH alias AS (subquery)); many engines optimize them similarly today.