Derived Tables in SQL
Introduction
A derived table is a temporary, table-shaped result produced by a subquery in the FROM clause. The surrounding query can read that result as though it were a regular table, which makes it possible to filter calculated values, join aggregated data, rank rows, or divide a difficult requirement into understandable stages. The derived result belongs only to the statement in which it appears. It does not create a permanent database object and does not imply that the database must write the rows to physical storage.
Derived tables are especially useful when one query block must first calculate an intermediate result and another query block must process it. For example, an inner query can calculate average salary by department, while the outer query keeps only departments whose average exceeds a threshold. The same pattern supports top-N analysis, latest-record selection, duplicate investigation, reporting summaries, and calculations over window-function results.
This tutorial develops a practical mental model rather than treating derived tables as syntax alone. It explains aliases, column visibility, joins, grouping, ordering, compatibility, optimizer transformations, indexing, execution plans, common errors, and design choices. The goal is to know not only how to write a derived table, but also when it improves a query and when a simpler statement, common table expression, temporary table, view, or direct join is the better choice.
What Is a Derived Table?
A derived table is a subquery that appears where a table expression is allowed in the FROM clause. Its selected columns become the columns visible to the outer query, and its table alias becomes the name used to qualify those columns. Conceptually, the inner query produces rows first and the outer query then consumes them. That logical explanation is useful for reasoning, although the optimizer may merge, reorder, or otherwise transform the work physically.
SELECT
department_id,
average_salary
FROM (
SELECT
department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS department_summary;
The inner query groups employees and calculates one average for each department. The outer query reads the resulting columns from department_summary. The alias identifies the derived table, while average_salary names a calculated column that would otherwise be awkward or impossible to reference clearly.
Core Structure and Syntax
Every derived table has an inner query enclosed in parentheses, a position inside the outer FROM clause, and a table alias. A portable pattern assigns explicit names to every calculated expression and uses a descriptive table alias.
SELECT dt.column1, dt.calculated_value
FROM (
SELECT
column1,
expression AS calculated_value
FROM source_table
WHERE source_condition
) AS dt
WHERE dt.calculated_value > 0;
| Component | Purpose |
|---|---|
Inner SELECT | Produces the intermediate relational result |
| Parentheses | Define the boundary of the subquery |
| Derived-table alias | Gives the intermediate result a usable name |
| Column aliases | Name calculations exposed to the outer query |
| Outer query | Filters, joins, groups, orders, or projects the result |
Why Derived Tables Are Useful
Many requirements contain more than one logical stage. A report may need to aggregate transaction rows, compare each aggregate with a threshold, join the qualifying summaries to master data, and finally sort the presentation. Trying to express every stage in one flat query can repeat expressions or blur the intended row grain. A derived table creates a named boundary between stages.
The boundary can improve readability, but it is not automatically a performance optimization. The database may merge the inner query into the outer query, materialize it temporarily, or choose another strategy. Use the construct because it expresses the requirement clearly, then evaluate performance with realistic data and execution plans.
Sample Data Used in the Examples
Assume an employees table contains employee ID, name, department ID, salary, and manager ID. Departments 10, 20, and 30 represent Engineering, Sales, and Finance, while department 40 represents Support and currently has no employees. A separate orders table contains customer ID, amount, status, and order date. These small examples make row grain visible, but the same designs apply to production-scale data.
| Department | Employees | Salaries | Average |
|---|---|---|---|
| 10 - Engineering | 3 | 60000, 50000, 70000 | 60000 |
| 20 - Sales | 2 | 80000, 60000 | 70000 |
| 30 - Finance | 2 | 90000, 75000 | 82500 |
| 40 - Support | 0 | None | NULL |
A Basic Derived Table Example
The following query places a simple employee projection in a derived table and filters it outside. It is valid, but it also demonstrates that valid syntax is not always good design.
SELECT employee_id, employee_name, salary
FROM (
SELECT employee_id, employee_name, salary
FROM employees
) AS employee_data
WHERE salary > 65000;
A direct query with WHERE salary > 65000 expresses the same requirement more simply. Introduce a derived table when the inner query adds a meaningful stage, such as aggregation, ranking, limiting, unioning, or a calculation that the outer query must consume.
Aliases and Column Visibility
A derived table should always receive a meaningful alias. Some systems require one, and even where syntax is permissive, the alias documents the result's purpose. Names such as department_summary, customer_totals, and ranked_orders explain more than aliases such as x or t1.
The outer query can reference only columns returned by the inner SELECT. A source column used inside the subquery does not automatically remain visible outside it. Calculated expressions should also have column aliases so the outer query can reference them reliably.
SELECT salary_summary.department_id,
salary_summary.average_salary
FROM (
SELECT department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS salary_summary;
Filtering Aggregated Results
A classic use case is filtering a calculated aggregate. The inner query establishes one row per department, and the outer query filters the named average.
SELECT department_id, average_salary
FROM (
SELECT department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS salary_summary
WHERE average_salary > 65000;
Sales and Finance qualify with averages of 70000 and 82500. A HAVING AVG(salary) > 65000 clause could solve this exact example without a derived table. The staged form becomes more valuable when the aggregated result needs another join, a second aggregation, several calculated filters, or reuse within a larger query block.
GROUP BY and HAVING Inside a Derived Table
The inner query can use both GROUP BY and HAVING. WHERE filters source rows before grouping, while HAVING filters groups after aggregation. The outer query then works only with the groups that survive.
SELECT department_id, employee_count
FROM (
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 3
) AS department_counts;
Engineering is returned because it has three employees. Keep the grain explicit: after the inner grouping, each derived row represents one department, not one employee.
Joining a Derived Table
One of the strongest uses of a derived table is joining an aggregate to descriptive master data. The aggregate preserves one row per department, while the departments table supplies the department name.
SELECT d.department_name,
e.employee_count
FROM departments AS d
INNER JOIN (
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
) AS e
ON e.department_id = d.department_id;
An inner join returns only departments represented in the employee summary. Support is absent because the derived table has no row for department 40. This behavior follows join semantics, not a limitation of derived tables.
Preserving Missing Groups with LEFT JOIN
Use a left join when the report must include departments with no employees. The departments table becomes the preserved side, and COALESCE converts a missing aggregate to zero.
SELECT d.department_id,
d.department_name,
COALESCE(e.employee_count, 0) AS employee_count
FROM departments AS d
LEFT JOIN (
SELECT department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
) AS e
ON e.department_id = d.department_id;
This returns Support with a count of zero. Do not move conditions on the optional derived table carelessly into the outer WHERE clause, because that can reject null-extended rows and make the left join behave like an inner join.
Multiple Aggregates in One Summary
A single grouped derived table can calculate count, minimum, maximum, sum, and average together. That is usually better than scanning the same employee data in several independent derived tables.
SELECT department_id,
employee_count,
minimum_salary,
maximum_salary,
average_salary
FROM (
SELECT department_id,
COUNT(*) AS employee_count,
MIN(salary) AS minimum_salary,
MAX(salary) AS maximum_salary,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS salary_statistics;
This pattern supports dashboards and reconciliation reports because each row carries a consistent set of measures at one declared grain.
Calculated Columns and Outer Filtering
A derived table can name a business calculation once and let the outer query filter or display it. The following example calculates a ten percent annual bonus and returns employees whose bonus exceeds 7000.
SELECT employee_name, salary, annual_bonus
FROM (
SELECT employee_name,
salary,
salary * 0.10 AS annual_bonus
FROM employees
) AS employee_compensation
WHERE annual_bonus > 7000;
This avoids repeating a long expression. Some databases also support alternatives such as CROSS APPLY, LATERAL, or reusable expressions, but a derived table remains broadly understandable and portable.
ORDER BY and Final Result Order
Place the final ORDER BY in the outermost query when output order matters. An inner ordering may be accepted when paired with row limiting, but it does not generally guarantee the order returned by the outer query.
SELECT department_id, average_salary
FROM (
SELECT department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS department_summary
ORDER BY average_salary DESC;
Relational results are unordered unless the final query requests an order. Never rely on an observed development result remaining stable without an outer ORDER BY.
Top-N Queries with Row Limiting
A derived table is valuable when a limited set requires additional processing. This MySQL and PostgreSQL example selects the five highest-paid employees and then calculates their average salary.
SELECT AVG(salary) AS top_five_average_salary
FROM (
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC, employee_id DESC
LIMIT 5
) AS top_five_employees;
The inner query must use deterministic ordering. The employee ID provides a tie-breaker when salaries are equal. SQL Server commonly uses TOP or OFFSET ... FETCH, while Oracle and standard-oriented dialects may use FETCH FIRST. Adapt row-limiting syntax to the target database.
Window Functions Inside Derived Tables
Window functions are a natural partner for derived tables. A window result normally cannot be referenced in the same query block's WHERE clause because filtering occurs logically before window calculation. The inner query computes the window value; the outer query filters it.
SELECT employee_id,
employee_name,
department_id,
salary
FROM (
SELECT employee_id,
employee_name,
department_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, employee_id DESC
) AS row_num
FROM employees
) AS ranked_employees
WHERE row_num <= 2;
This returns the two highest-paid employees in each department. It is a common reporting and interview pattern because it combines partitioning, deterministic ordering, and post-window filtering.
ROW_NUMBER, RANK, and DENSE_RANK
| Function | Tie behavior | Typical use |
|---|---|---|
ROW_NUMBER() | Assigns every row a unique sequence | Exactly N rows per group or one latest row |
RANK() | Ties share a rank and later ranks contain gaps | Competition-style ranking |
DENSE_RANK() | Ties share a rank without later gaps | Top N distinct value levels |
Choose based on the business meaning of ties. If two employees share the highest salary, filtering RANK() = 1 returns both, while filtering ROW_NUMBER() = 1 returns one according to the complete ordering.
Selecting the Latest Record per Group
To retrieve each customer's latest order, rank orders within each customer and keep row one. Include a deterministic tie-breaker because two orders may share a timestamp.
SELECT order_id, customer_id, order_date, total_amount
FROM (
SELECT order_id,
customer_id,
order_date,
total_amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS row_num
FROM orders
) AS ranked_orders
WHERE row_num = 1;
This pattern also retrieves the latest account status, newest test execution, current device reading, or most recent API event per key.
Detecting and Retrieving Duplicates
An aggregate derived table can identify duplicate values and then join back to retrieve complete records. The first query block defines duplicate email addresses; the outer query returns every user associated with them.
SELECT u.user_id, u.user_name, u.email
FROM users AS u
JOIN (
SELECT email
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
) AS duplicate_emails
ON duplicate_emails.email = u.email;
A direct grouped query is enough when only the duplicate email and count are required. The join-back pattern is useful for data-quality investigation because it restores row-level details after group-level detection.
Multiple Derived Tables in One Query
A query may join several derived tables, but first ask whether they scan the same source at the same grain. Employee count and average salary by department can be calculated in one grouped query instead of two. Combining them avoids duplicated work and keeps the measures consistent.
SELECT d.department_name,
s.employee_count,
s.average_salary
FROM departments AS d
LEFT JOIN (
SELECT department_id,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS s
ON s.department_id = d.department_id;
Separate derived tables are appropriate when sources, filters, or grains genuinely differ. Name each one according to its role and verify join cardinality before combining them.
UNION and UNION ALL in a Derived Table
A set operation can produce a derived table that the outer query filters or aggregates. UNION removes duplicates, while UNION ALL preserves them and usually avoids duplicate-elimination work.
SELECT region, SUM(amount) AS total_amount
FROM (
SELECT region, amount FROM current_sales
UNION ALL
SELECT region, amount FROM archived_sales
) AS all_sales
GROUP BY region;
Every branch must return compatible columns in the same logical positions. Choose UNION ALL when duplicated rows are valid and totals must include them. Use UNION only when set-level duplicate removal is part of the requirement.
CASE Expressions and Business Categories
The inner query can translate raw values into categories, and the outer query can group or filter those categories without repeating the CASE expression.
SELECT salary_band, COUNT(*) AS employee_count
FROM (
SELECT CASE
WHEN salary >= 80000 THEN 'High'
WHEN salary >= 60000 THEN 'Medium'
ELSE 'Entry'
END AS salary_band
FROM employees
) AS classified_employees
GROUP BY salary_band;
Keep category boundaries explicit and test exact boundary values. If the same classification is reused throughout the organization, a maintained reference table or governed semantic layer may be better than repeating the logic across reports.
Customer Spending Analysis
A customer summary can aggregate only completed orders and then join to customers. The filter belongs inside the derived table because it defines which transactions contribute to spending.
SELECT c.customer_id,
c.customer_name,
COALESCE(s.total_spent, 0) AS total_spent
FROM customers AS c
LEFT JOIN (
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
) AS s
ON s.customer_id = c.customer_id
WHERE COALESCE(s.total_spent, 0) >= 1000;
This example separates transaction eligibility, customer-level aggregation, master-data enrichment, and final qualification. Each stage has a clear grain and purpose.
Monthly Sales and Percentage Calculations
Reporting often starts with a derived table that creates one row per month. An outer query can calculate percentages, compare each month with a target, or apply another window function to derive month-over-month change.
SELECT sales_month,
monthly_total,
100.0 * monthly_total
/ SUM(monthly_total) OVER () AS percent_of_total
FROM (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
SUM(total_amount) AS monthly_total
FROM orders
WHERE status = 'COMPLETED'
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
) AS monthly_sales;
Include year when data spans more than one year; grouping by month number alone incorrectly combines January from different years. Protect ratio calculations from division by zero with NULLIF where appropriate.
Practical Uses Across Domains
Inventory systems use derived tables to summarize reserved and available quantities before joining product details. Banking reports aggregate credits and debits by account, then identify balances outside expected thresholds. Student systems calculate course averages before classifying performance. Software testing dashboards summarize passed, failed, and skipped executions before joining suite ownership. API monitoring reports aggregate response count, error count, and latency by endpoint before applying service-level thresholds.
The syntax is the same across these domains. What changes is the grain, qualifying rules, null meaning, and acceptable time window. State those decisions before writing the query.
Derived Table vs Ordinary Subquery
Derived table is a role played by a subquery in the FROM clause. A scalar subquery returns one value in a select list or expression. A predicate subquery supports conditions such as EXISTS or IN. A correlated subquery refers to the current row of an outer query. These terms describe placement and behavior rather than entirely separate technologies.
Use the form that matches the problem. A derived table is appropriate when the intermediate result behaves like a relation with rows and columns that another query block must consume.
Derived Table vs Common Table Expression
A common table expression, introduced with WITH, can express the same logic as many derived tables. A CTE gives the intermediate result a name before the main statement and is often easier to read when several later clauses reference it or when multiple stages would otherwise create deep nesting.
| Concern | Derived table | CTE |
|---|---|---|
| Location | Inline in FROM | Named before the main statement |
| Scope | Current query block | Current statement |
| Reuse | Normally referenced once | May be referenced multiple times, depending on dialect |
| Readability | Good for one compact stage | Good for several named stages |
| Recursion | Not recursive by itself | Recursive CTEs may be supported |
Neither form is automatically faster. Optimizers may inline, merge, or materialize either form according to database rules and query characteristics.
Derived Table vs Temporary Table and View
A derived table lasts for one statement and requires no separately managed object. A temporary table can survive across several statements in a session or transaction, can often be indexed, and is useful when an expensive intermediate result must be reused or inspected. It also introduces creation, population, statistics, cleanup, and transaction considerations.
A view stores a reusable query definition in the database schema. It supports shared abstraction and security, but it requires governance and does not necessarily store result rows. Use a derived table for local one-statement logic, a temporary table for reusable staged data, and a view for a governed reusable definition.
Derived Table vs Direct JOIN
A direct join combines source rows. A derived table can first reshape one side so that its grain is safe for the join. For example, joining departments directly to employees produces one row per employee, while joining departments to an employee-count derived table produces one row per department.
The derived table does not replace joins; it prepares one of the inputs. Always verify whether the join is one-to-one, one-to-many, or many-to-many at the point where it occurs. Unexpected row multiplication is usually a grain or join-condition problem.
Materialization, Merging, and Predicate Pushdown
Materialization means the engine computes an intermediate result and stores it temporarily for later operations. Merging means the optimizer incorporates the derived query into the surrounding query rather than treating it as a separate physical stage. Predicate pushdown means an outer filter is moved closer to the source so fewer rows continue through later operations.
These are optimizer decisions, not guarantees implied by the SQL text. Grouping, distinctness, window functions, set operations, row limits, nondeterministic expressions, and database-specific rules can restrict merging or pushdown. A derived table may therefore behave as an optimization boundary in one system or version but not in another.
Performance and Indexing
Indexes are created on base tables, not on a statement-scoped derived table. Useful indexes support the inner query's filters, joins, grouping keys, and ordering, as well as the outer query's joins to the derived result. A composite index should reflect real access patterns rather than simply collecting every referenced column.
Reduce rows and columns as early as correctness allows. Avoid selecting unused wide values, repeated scans of the same source, accidental duplicate elimination, and unnecessary nesting. However, do not push a filter across an aggregate or outer join if doing so changes meaning. Logical correctness comes before row reduction.
Performance depends on data volume, selectivity, skew, memory, statistics, indexes, database version, and concurrent workload. Claims that derived tables are always faster or always slower are not defensible.
Reading the Execution Plan
Use EXPLAIN, EXPLAIN ANALYZE, or the database's actual-plan tooling to see what happened. Look for estimated and actual row counts, access methods, join algorithms, sorts, hashes, spills, materialization, repeated loops, and filters applied later than expected.
A large difference between estimated and actual rows can indicate stale statistics, correlated data, skew, or an expression the optimizer cannot estimate well. Compare plans under realistic parameter values. A fast development query over seven sample rows does not predict production behavior over hundreds of millions of transactions.
Database Compatibility
Core derived-table syntax is broadly supported, but details differ. MySQL requires aliases for derived tables and has optimizer switches that influence merging. PostgreSQL offers LATERAL for derived expressions that reference earlier FROM items. SQL Server provides APPLY for related patterns and uses different row-limiting syntax. Oracle versions differ in support for aliases with the optional AS keyword on table expressions.
Window functions, row-value comparisons, date extraction, full outer joins, and row limiting also vary. Write for the actual platform and version, and test portability rather than assuming standard-looking syntax behaves identically everywhere.
Common Mistakes
- Omitting the derived-table alias or using an alias that hides its purpose.
- Referencing a source column that the inner query did not return.
- Leaving calculated expressions without stable column aliases.
- Assuming the result persists after the statement finishes.
- Assuming every derived result is physically materialized.
- Filtering before aggregation when the requirement concerns an aggregate.
- Joining results at incompatible grains and multiplying rows.
- Believing an inner
ORDER BYguarantees final order. - Ignoring null values in arithmetic, joins, and aggregate results.
- Nesting many levels when a CTE or simpler query would be clearer.
- Using database-specific
LIMIT, date, or alias syntax without checking compatibility. - Adding a derived table where a direct query communicates the requirement better.
Debugging Checklist
Run the inner query independently and confirm its row count, columns, data types, nulls, duplicates, and grain. Add outer joins one at a time and compare row counts after each step. Verify every join key, especially tenant, version, date-effective, and composite-key columns. Test empty input, one row, multiple matches, ties, null keys, and boundary values.
When aggregates are wrong, inspect whether filtering occurs before or after grouping. When ranks are unstable, add deterministic tie-breakers. When performance is poor, capture the actual plan and measure source rows versus derived rows rather than guessing from syntax.
Best Practices
- Use a derived table only when it creates a meaningful logical stage.
- Name the table and calculated columns according to business purpose.
- Return only the columns required by the outer query.
- State and preserve the row grain at every query boundary.
- Keep final ordering in the outermost query.
- Use deterministic ordering for top-N and latest-row logic.
- Combine aggregates that scan the same source at the same grain.
- Choose a CTE when named stages improve readability.
- Choose a temporary table when an intermediate result needs reuse or indexing.
- Inspect plans and test realistic data before making performance claims.
Interview Questions and Answers
What is a derived table?
It is a subquery in the FROM clause whose result is treated as a table by the surrounding query.
Is a derived table permanent?
No. It is scoped to the statement and does not create a permanent schema object.
Does a derived table require an alias?
Requirements vary, but a table alias is mandatory in common systems such as MySQL and is a strong portability and readability practice everywhere.
Can it contain joins, grouping, and window functions?
Yes. It can contain most valid query operations allowed by the database, including joins, grouping, set operations, calculations, and window functions.
Are derived tables faster than subqueries or CTEs?
There is no universal ranking. A derived table is itself a subquery, and optimizers may transform derived tables and CTEs into similar plans. Measure the actual workload.
What is materialization?
Materialization is a physical strategy in which the engine computes and temporarily stores an intermediate result instead of fully merging it into the surrounding plan.
Why are derived tables useful with window functions?
They create an outer query level where a calculated rank or row number can be filtered after the window function has been evaluated.
Practical Decision Guide
| Requirement | Recommended starting point |
|---|---|
| One compact intermediate result used once | Derived table |
| Several named stages or recursive logic | CTE |
| Intermediate rows reused across statements | Temporary table |
| Shared governed query definition | View |
| Only simple row filtering is needed | Direct query |
| Related columns must be returned | Join, possibly to a derived summary |
| Top N rows per group | Window function in a derived table or CTE |
| Aggregate must be filtered or joined | Grouped derived table or CTE |
Complete Mental Model
Base tables
|
v
Inner query
- filter source rows
- join sources
- group or rank
- name calculated columns
|
v
Derived relational result
- known columns
- declared grain
- statement scope
|
v
Outer query
- filter calculated values
- join descriptive data
- aggregate again when required
- order the final result
This model describes logical responsibilities. The optimizer remains free to merge stages, push predicates, reorder joins, or materialize results as long as observable SQL semantics are preserved.
Key Takeaway
A derived table is a practical way to turn one query's result into the input of another query block. It is most valuable when the intermediate result has a clear purpose and grain, such as a departmental summary, ranked order set, monthly total, duplicate key list, or top-N selection. It is not a permanent table and not an automatic performance improvement.
Use meaningful aliases, expose only required columns, preserve join cardinality, put final ordering at the outer level, and choose deterministic window ordering. Compare the design with direct queries, CTEs, temporary tables, and views. Once the query is logically correct and readable, evaluate indexes and actual execution plans with production-like data.