Derived Tables in SQL

When learning SQL, most people focus on SELECT statements, JOINs, GROUP BY, and Window Functions. However, one concept that frequently appears in real-world projects and SQL interviews is the Derived Table.

Many SQL interview questions become much easier when you know how to use derived tables effectively.

In this article, we’ll explore what a derived table is, why it’s useful, how it works internally, and how it differs from subqueries, CTEs, and temporary tables.

What is a Derived Table?

A Derived Table is a temporary result set created by a subquery inside the FROM clause.

The output of the subquery behaves like a virtual table that can be queried by the outer SQL statement.

Think of it as:

First create a temporary table using a query, then use that temporary table in another query.

General Syntax:

SELECT *
FROM (
        SELECT column1,
               column2
        FROM table_name
     ) AS derived_table;

The alias (derived_table) is mandatory because SQL needs a name to reference the temporary result.

Understanding with a Simple Example

Suppose we have an employee table.

+--------+---------+--------+
| emp_id | name    | salary |
+--------+---------+--------+
| 1      | Raj     | 50000  |
| 2      | Amit    | 70000  |
| 3      | Neha    | 60000  |
| 4      | Priya   | 80000  |
+--------+---------+--------+

Requirement:

Find employees whose salary is greater than the average salary.

Solution:

SELECT e.*
FROM employees e
JOIN (
        SELECT AVG(salary) AS avg_salary
        FROM employees
     ) a
ON e.salary > a.avg_salary;

Step 1: SQL executes the inner query.

SELECT AVG(salary) AS avg_salary
FROM employees;

Output:

+------------+
| avg_salary |
+------------+
| 65000      |
+------------+

Step 2: This result becomes a temporary virtual table.

Step 3: The outer query compares employee salaries with the average salary.

Final Output:

+--------+-------+--------+
| emp_id | name  | salary |
+--------+-------+--------+
| 2      | Amit  | 70000  |
| 4      | Priya | 80000  |
+--------+-------+--------+

Why Do We Need Derived Tables?

Derived tables help solve complex SQL problems in a structured and readable way.

Common use cases include:

1. Aggregating data before filtering.
2. Simplifying complicated joins.
3. Breaking large queries into smaller pieces.
4. Reusing calculated values.
5. Solving interview questions involving averages, rankings, and summaries.

Aggregation Followed by Filtering

Consider an Orders table.

+----------+---------+--------+
| order_id | cust_id | amount |
+----------+---------+--------+
| 101      | 1       | 500    |
| 102      | 1       | 300    |
| 103      | 2       | 700    |
| 104      | 3       | 900    |
+----------+---------+--------+

Requirement:

Find customers whose total spending exceeds 700.

Solution:

SELECT *
FROM (
        SELECT cust_id,
               SUM(amount) AS total_spent
        FROM orders
        GROUP BY cust_id
     ) d
WHERE total_spent > 700;

Output:

+---------+-------------+
| cust_id | total_spent |
+---------+-------------+
| 1       | 800         |
| 3       | 900         |
+---------+-------------+

The derived table first calculates customer spending and then the outer query filters the result.

Derived Tables with JOINs

One of the most common interview scenarios involves comparing employees against department averages.

Employee Table:

+--------+---------+---------------+--------+
| emp_id | name    | department_id | salary |
+--------+---------+---------------+--------+
| 1      | Raj     | 101           | 50000  |
| 2      | Amit    | 101           | 70000  |
| 3      | Neha    | 102           | 60000  |
| 4      | Priya   | 102           | 80000  |
+--------+---------+---------------+--------+

Requirement:

Find employees earning more than their department’s average salary.

Solution:

SELECT e.*
FROM employees e
JOIN (
        SELECT department_id,
               AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
     ) d
ON e.department_id = d.department_id
AND e.salary > d.avg_salary;

This is one of the most frequently asked SQL interview questions.

Derived Table vs Subquery

Many beginners think both are identical, but they serve different purposes.

Subquery Example:

SELECT *
FROM employees
WHERE salary >
(
    SELECT AVG(salary)
    FROM employees
);

Derived Table Example:

SELECT *
FROM employees e
JOIN (
        SELECT AVG(salary) AS avg_salary
        FROM employees
     ) a
ON e.salary > a.avg_salary;

Comparison:

+-------------------+-------------------+-------------------+
| Feature           | Subquery          | Derived Table     |
+-------------------+-------------------+-------------------+
| Location          | WHERE/SELECT      | FROM Clause       |
| Output            | Value/Result Set  | Virtual Table     |
| Alias Required    | No                | Yes               |
| Complexity        | Simple Logic      | Complex Logic     |
| Join Capability   | Limited           | Easy              |
+-------------------+-------------------+-------------------+

Derived Table vs CTE

The same logic can often be written using a CTE.

Get Rajesh Umrao-Data&AI’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Derived Table:

SELECT *
FROM (
        SELECT department_id,
               AVG(salary) avg_salary
        FROM employees
        GROUP BY department_id
     ) d;

CTE:

WITH dept_avg AS
(
    SELECT department_id,
           AVG(salary) avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT * FROM dept_avg;

Comparison:

+---------------------+------------------+------------------+
| Feature             | Derived Table    | CTE              |
+---------------------+------------------+------------------+
| Readability         | Medium           | High             |
| Reusability         | No               | Yes              |
| Multiple Reference  | Difficult        | Easy             |
| Nested Logic        | Hard             | Easy             |
| Interview Usage     | Very Common      | Very Common      |
+---------------------+------------------+------------------+

In modern SQL development, CTEs are often preferred because they improve readability.

Derived Table vs Temporary Table

Many people confuse these concepts.

+-------------------+------------------+------------------+
| Feature           | Derived Table    | Temp Table       |
+-------------------+------------------+------------------+
| Physical Storage  | No               | Yes              |
| Lifetime          | Query Execution  | Session          |
| Reusable          | No               | Yes              |
| Performance       | Good             | Better for Reuse |
| Maintenance       | Easy             | Requires Cleanup |
+-------------------+------------------+------------------+

A derived table disappears immediately after query execution.

A temporary table remains available until the session ends or it is dropped.

Can We Nest Derived Tables?

Yes.

Example:

SELECT *
FROM (
        SELECT *
        FROM (
                SELECT emp_id,
                       salary
                FROM employees
             ) a
     ) b;

Although possible, excessive nesting reduces readability and should generally be avoided.

Common Interview Questions

1. Is Alias Mandatory?

Yes.

Correct:

SELECT *
FROM (
        SELECT *
        FROM employees
     ) d;

Incorrect:

SELECT *
FROM (
        SELECT *
        FROM employees
     );

Most databases will throw an error.

2. Can We Use GROUP BY Inside a Derived Table?

Absolutely.

SELECT *
FROM (
        SELECT department_id,
               COUNT(*) AS total_employees
        FROM employees
        GROUP BY department_id
     ) d;

3. Can We Join Multiple Derived Tables?

Yes.

SELECT *
FROM derived_table_1 d1
JOIN derived_table_2 d2
ON d1.id = d2.id;

4. Can Derived Tables Improve Readability?

Yes.

They allow you to separate calculations from filtering and joining logic, making complex queries easier to understand.

Real-World Data Analyst Example

Imagine you’re analyzing sales performance.

Requirement:

Find products whose sales are higher than the average product sales.

Step 1: Calculate total sales per product.

Step 2: Store the result in a derived table.

Step 3: Compare each product’s sales with the overall average.

This pattern appears frequently in dashboards, reporting systems, and analytics projects.

Advantages of Derived Tables

✓ Simplify complex queries

✓ Improve readability

✓ Help perform aggregation before filtering

✓ Useful for interview problems

✓ Easy to combine with joins

✓ Great for reporting and analytics queries

Disadvantages of Derived Tables

✗ Cannot be reused within the same query

✗ Deep nesting can reduce readability

✗ Large derived tables may affect performance

✗ CTEs are often easier to maintain

Key Takeaways

A Derived Table is a temporary virtual table created using a subquery inside the FROM clause.

It allows SQL developers and data analysts to break complex logic into smaller and more manageable steps.

Whenever you need to aggregate data first and then perform additional filtering, joining, or calculations, a derived table is often one of the best solutions.

If you’re preparing for SQL interviews, make sure you’re comfortable using derived tables because they frequently appear in medium and advanced SQL questions.

Interview Definition

A Derived Table is a temporary virtual table generated by a subquery in the FROM clause and used by the outer query for further processing during query execution.

✍️ Explained by Rajesh Umrao

Join WhatsApp Community: Link

👨‍💻Preparing for Interview :

📺 YouTube: @rajesh_data_ai
Please Extend your Support by Subscribing the Channel: @rajesh_data_ai

#SQL #DataEngineering #Analytics #BigData #ETL #DataAnalytics #SQLTips #DataPipeline #LearningSQL #DataAnalyst #DataScience #BusinessIntelligence #RajeshUmrao