Derived Tables

Derived tables are temporary tables created within a SQL query. They exist only during the execution of the query and are not stored permanently in the database. Derived tables are useful for simplifying complex queries by breaking them into smaller, manageable parts.

Purpose of Derived Tables

• Simplify complex queries
• Perform intermediate calculations
• Enable filtering and aggregation before joining with other tables
• Avoid creating permanent temporary tables in the database

Syntax

A derived table is created using a subquery in the FROM clause. Example:

SELECT dt.column1, dt.column2
FROM (
    SELECT column1, column2
    FROM table_name
    WHERE condition
) AS dt;

• The subquery inside parentheses generates the derived table.
• AS dt gives a name (alias) to the derived table for reference in the outer query.

Key Points

• Derived tables are temporary and only exist for the duration of the query.
• They must have an alias; otherwise, SQL will return an error.
• They can be used for filtering, joining, and aggregation.
• Nested derived tables are possible, but may affect performance if overused.

Example 1: Using Derived Table for Filtering

SELECT dt.employee_id, dt.total_sales
FROM (
    SELECT employee_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
) AS dt
WHERE dt.total_sales > 10000;

Explanation: The inner query calculates total sales per employee. The outer query filters employees with sales greater than 10,000.

Example 2: Using Derived Table with Join

SELECT e.employee_name, dt.total_sales
FROM employees e
JOIN (
    SELECT employee_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
) AS dt
ON e.employee_id = dt.employee_id;

Explanation: The derived table calculates total sales per employee, which is then joined with the employees table to get employee names.

Advantages

• Simplifies complex queries
• Reduces the need for multiple temporary tables
• Keeps queries organized and readable

Best Practices

• Always use meaningful aliases for derived tables
• Avoid using too many nested derived tables to maintain performance
• Use derived tables when temporary results are needed only within the query
• Consider using Common Table Expressions (CTEs) for readability if queries are very complex