SQL Interview Prep · Lesson

Subqueries in the FROM Clause (Derived Tables)

Wrapping a query as a virtual table and why aliases are mandatory.

Subqueries in the FROM Clause (Derived Tables) is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What a Derived Table Is

A subquery in the FROM clause is called a derived table (or inline view). Instead of returning a single value, it returns a whole result set that the outer query treats as if it were a real table.

It can have many rows and many columns.
You query it, join it, and filter it like any table.

Interviewers use derived tables to test whether you can break a problem into stages.

Aliases Are Mandatory

The number-one gotcha: a derived table must have an alias. Without one, most engines reject the query.

MySQL: Every derived table must have its own alias.
Postgres: subquery in FROM must have an alias.

Give it a name (here dept_avg) and you can reference its columns by that name.

SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS dept_avg;

Why Pre-Aggregate in a Derived Table

A frequent interview problem: show each employee alongside their department's average salary. You cannot mix the detail row with an aggregate directly without grouping issues.

The clean approach is to compute the per-department average in a derived table, then join it back to the detail rows. The derived table collapses to one row per department first.

SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d ON e.dept_id = d.dept_id;

Filtering on an Aggregate Result

Derived tables let you filter on a computed aggregate without HAVING gymnastics in the outer query. Suppose we want only departments whose average salary exceeds 60000.

We aggregate inside, then apply a plain WHERE on the derived column outside. The outer query sees avg_salary as an ordinary column.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d
WHERE avg_salary > 60000;

Two Levels of Aggregation

Derived tables shine when you need an aggregate of an aggregate — a classic interview ask: what is the average of the per-department average salaries?

You cannot nest AVG(AVG(...)) directly. The inner query produces one average per department; the outer query averages those.

SELECT AVG(avg_salary) AS avg_of_dept_avgs
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS d;

Naming Computed Columns

Any expression in a derived table needs an alias if you want to reference it outside. The inner salary * 12 would otherwise have a database-assigned name you cannot rely on.

Always alias computed columns — interviewers notice when you reference an un-aliased expression and assume a column name that may not exist.

SELECT name, annual_salary
FROM (
  SELECT name, salary * 12 AS annual_salary
  FROM employees
) AS yearly
WHERE annual_salary > 100000;

Joining Two Derived Tables

You can join multiple derived tables together. Here we compare each department's headcount with its total payroll by joining two pre-aggregated subqueries.

Each derived table answers one sub-question; the join stitches them into the final report. This staged thinking is exactly what mid-level interviews reward.

SELECT c.dept_id, c.headcount, p.payroll
FROM (
  SELECT dept_id, COUNT(*) AS headcount
  FROM employees GROUP BY dept_id
) AS c
JOIN (
  SELECT dept_id, SUM(salary) AS payroll
  FROM employees GROUP BY dept_id
) AS p ON c.dept_id = p.dept_id;

Scope: The Outer Query Cannot See Inside

An important rule: the outer query can only reference the columns the derived table exposes in its SELECT list. Columns used only inside the subquery are invisible outside.

If the inner query selects dept_id and avg_salary, then salary or name are not available outside — they were consumed by the aggregation. Interviewers probe this scoping boundary.

SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
) AS d;

Derived Table vs CTE

A derived table and a Common Table Expression (CTE) often produce the same plan. Interviewers may ask why you would choose one:

Derived table: inline, fine for one-off use.
CTE (WITH): named at the top, readable, and reusable if referenced multiple times.

For deeply nested logic, a CTE pipeline reads top-to-bottom; a derived table reads inside-out.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees GROUP BY dept_id
)
SELECT * FROM dept_avg WHERE avg_salary > 60000;

The LATERAL / Correlated FROM Subquery

Normally a FROM subquery cannot reference the outer query's rows. LATERAL (Postgres) or CROSS APPLY (SQL Server) lifts that restriction, letting the derived table run per outer row.

This powers per-row top-N lookups. Knowing the keyword exists signals senior awareness even at a mid-level screen.

SELECT d.dept_name, top_emp.name, top_emp.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT name, salary FROM employees e
  WHERE e.dept_id = d.id
  ORDER BY salary DESC LIMIT 1
) AS top_emp;

Interview Soundbite

If asked about FROM-clause subqueries, say: "A derived table is a subquery in FROM that returns a result set the outer query uses like a table. It must have an alias, the outer query can only see the columns it selects, and it is ideal for pre-aggregating before a join or for aggregating an aggregate."

Add that LATERAL allows it to reference outer rows, and you have covered every angle.

Quick Check

Pick the statement that is always required for a FROM-clause subquery.

Recap

Derived tables, locked in:

A FROM subquery returns a virtual table — many rows, many columns.
It must have an alias; the outer query sees only its selected columns.
Use it to pre-aggregate before a join, to filter on aggregates, or to aggregate an aggregate.
A CTE is the readable named alternative; LATERAL/CROSS APPLY let it reference outer rows.

Next: set-membership subqueries with IN, ANY, and ALL.

Frequently asked questions

Is the “Subqueries in the FROM Clause (Derived Tables)” lesson free?

Yes — the full text of “Subqueries in the FROM Clause (Derived Tables)” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Subqueries in the FROM Clause (Derived Tables)”?

Wrapping a query as a virtual table and why aliases are mandatory. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Subqueries in the FROM Clause (Derived Tables)” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Scalar Subqueries in SELECT and WHERE
Subqueries in the FROM Clause (Derived Tables)
IN, ANY and ALL Subqueries
EXISTS vs IN Performance

← Back to SQL Interview Prep