An Essential Guide to MySQL Derived Table

An Essential Guide to MySQL Derived Table

A derived table in MySQL is a subquery that is used in the FROM clause of a query. It acts as a temporary table that is created on the fly and used for further querying. Derived tables are especially useful when you need to manipulate or filter data before joining or aggregating it.

Key Features of Derived Tables

1. Temporary in Nature: Exists only during the query execution.
2. Alias Required: Must always be assigned an alias.
3. Flexible Usage: This can be used with joins, aggregations, and filters.

Syntax

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

• Subquery in FROM: The derived table is created using a subquery.
• AS derived_table_alias: An alias is mandatory for the derived table.

Example Tables

Table: employees

+----+----------+------------+-------+
| id | name     | department | salary|
+----+----------+------------+-------+
| 1  | Alice    | IT         | 6000  |
| 2  | Bob      | HR         | 4000  |
| 3  | Charlie  | IT         | 5500  |
| 4  | Diana    | HR         | 4500  |
| 5  | Edward   | Sales      | 5000  |
+----+----------+------------+-------+

Table: departments

+----+------------+----------+
| id | name       | budget   |
+----+------------+----------+
| 1  | IT         | 15000    |
| 2  | HR         | 10000    |
| 3  | Sales      | 12000    |
+----+------------+----------+

1. Basic Derived Table

Find employees with salaries greater than the average salary:

SELECT name, salary
FROM (
    SELECT name, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
) AS high_earners;

Result

+----------+--------+
| name     | salary |
+----------+--------+
| Alice    | 6000   |
| Charlie  | 5500   |
+----------+--------+

2. Derived Table with Aggregation

Calculate the total salary for each department:

SELECT department, total_salary
FROM (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
) AS department_salaries;

Result

+------------+--------------+
| department | total_salary |
+------------+--------------+
| IT         | 11500        |
| HR         | 8500         |
| Sales      | 5000         |
+------------+--------------+

3. Joining a Derived Table

Find departments with employees whose total salary exceeds the department's budget:

SELECT d.name AS department, d.budget, t.total_salary
FROM (
    SELECT department, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department
) AS t
JOIN departments AS d ON t.department = d.name
WHERE t.total_salary > d.budget;

Result

+------------+--------+--------------+
| department | budget | total_salary |
+------------+--------+--------------+
| IT         | 15000  | 11500        |
| HR         | 10000  | 8500         |
+------------+--------+--------------+

4. Using Derived Table in Combination with Window Functions

Find the rank of employees based on salary within each department:

SELECT name, department, salary, rank
FROM (
    SELECT 
        name, 
        department, 
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM employees
) AS ranked_employees
WHERE rank = 1;

Result

+----------+------------+--------+------+
| name     | department | salary | rank |
+----------+------------+--------+------+
| Alice    | IT         | 6000   | 1    |
| Diana    | HR         | 4500   | 1    |
| Edward   | Sales      | 5000   | 1    |
+----------+------------+--------+------+

5. Filtering Derived Table Results

Find employees in the top 50% of salaries:

SELECT name, salary
FROM (
    SELECT name, salary, NTILE(2) OVER (ORDER BY salary DESC) AS salary_group
    FROM employees
) AS salary_distribution
WHERE salary_group = 1;

Result

+----------+--------+
| name     | salary |
+----------+--------+
| Alice    | 6000   |
| Charlie  | 5500   |
| Edward   | 5000   |
+----------+--------+

6. Combining Multiple Derived Tables

Compare the average salary of employees in IT and HR departments:

SELECT it_avg.salary AS it_average, hr_avg.salary AS hr_average
FROM (
    SELECT AVG(salary) AS salary
    FROM employees
    WHERE department = 'IT'
) AS it_avg,
(
    SELECT AVG(salary) AS salary
    FROM employees
    WHERE department = 'HR'
) AS hr_avg;

Result

+------------+-----------+
| it_average | hr_average|
+------------+-----------+
| 5750       | 4250      |
+------------+-----------+

Advantages of Derived Tables

1. Simplifies Queries:
   • Breaks down complex queries into manageable parts.
2. Reusable Logic:
   • Use the result of a subquery for further processing.
3. Dynamic Filtering:
   • Filter or aggregate data before using it in the main query.

Limitations

1. Performance:
   • Large or complex derived tables may impact performance.
2. No Indexes:
   • Derived tables are not indexed, which can slow down joins or filtering.
3. Temporary:
   • Cannot be reused outside the current query.

Performance Tips

1. Use Temporary Tables:
   • For frequently accessed derived tables, store results in temporary tables.
2. Optimize Subqueries:
   • Use indexes and avoid unnecessary columns in subqueries.
3. Analyze with EXPLAIN:
   • Check query execution plans to identify bottlenecks.

Use Cases

1. Reporting:
   • Summarizing or aggregating data before applying business logic.
2. Filtering Large Data:
   • Pre-filter data to improve the efficiency of the main query.
3. Dynamic Joins:
   • Create temporary datasets to join with other tables.

Let me know if you'd like additional examples or further clarification!