MySQL - Derived Tables

MySQL: Derived Tables and Common Table Expressions (CTEs)

What is a Derived Table?

A Derived Table is a virtual table that is generated from a SELECT statement within the FROM clause of another query. It’s essentially a subquery that you can immediately use as if it were a real table for the duration of that single query. It is not stored in the database and exists only during the execution of the statement.

Derived tables are useful for breaking down complex problems into smaller, logical steps, such as performing aggregations and then joining the results back to another table.

Prerequisite: Sample Data

For our examples, let’s assume we have a sales table.

CREATE TABLE sales (
    order_id INT PRIMARY KEY,
    employee_id INT NOT NULL,
    region VARCHAR(50),
    sale_amount DECIMAL(10, 2)
);

INSERT INTO sales VALUES
(1, 101, 'North', 1500.00),
(2, 102, 'South', 2200.00),
(3, 101, 'North', 800.00),
(4, 103, 'West', 3100.00),
(5, 102, 'South', 1800.00),
(6, 101, 'West', 2500.00);

Basic Syntax and Usage

A derived table must be given an alias using the AS keyword so the outer query can reference it.

SELECT ...
FROM (
    -- This is the subquery that creates the derived table
    SELECT ... FROM ...
) AS derived_table_alias;

Example: Calculating Average Sale Amount per Region

Let’s find the average sale amount for each region. First, we’ll create a derived table that calculates the total sales per region, and then the outer query will calculate the average of those totals.

SELECT AVG(regional_total) AS average_of_regional_sales
FROM (
    SELECT region, SUM(sale_amount) AS regional_total
    FROM sales
    GROUP BY region
) AS region_sales;

Output

average_of_regional_sales
3200.000000

Filtering and Aliasing in Derived Tables

You can treat the derived table just like a regular table, applying WHERE clauses, JOINs, and aliasing its columns.

Example: Find Employees with Total Sales Over $3000

SELECT
    employee_id,
    total_sales
FROM (
    SELECT employee_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
) AS employee_summary -- Alias for the derived table
WHERE total_sales > 3000.00;

Output

employee_id	total_sales
101	4800.00
102	4000.00
103	3100.00

A Modern Alternative: Common Table Expressions (CTEs)

While derived tables work, complex queries with multiple or nested derived tables can become very difficult to read. MySQL 8.0 and later support Common Table Expressions (CTEs), which provide a cleaner, more readable way to achieve the same result. A CTE is a named, temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

WITH cte_name AS (
    -- Subquery to define the CTE
    SELECT ... FROM ...
)
SELECT ... FROM cte_name;

Example: Rewriting the Employee Sales Query with a CTE

Let’s rewrite the previous example using a CTE. Notice how the logic is separated: first we define the employee_summary, then we select from it.

WITH employee_summary AS (
    SELECT employee_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
)
SELECT
    employee_id,
    total_sales
FROM employee_summary
WHERE total_sales > 3000.00;

The output is identical, but the query is arguably much easier to read and maintain.

CTEs vs. Derived Tables: Which to Use?

• Readability: CTEs are almost always more readable, especially for complex queries. They allow you to structure your logic sequentially.
• Reusability: A CTE can be referenced multiple times within the same query, whereas a derived table would need to be defined again.
• Recursion: CTEs support recursive queries, allowing you to work with hierarchical data (like organizational charts or parts assemblies), which is impossible with derived tables.
• Recommendation: For any modern MySQL development (version 8.0+), prefer CTEs over derived tables. They are more powerful, more readable, and represent the modern standard for writing complex SQL.

Practical Examples with Client Programs

Here’s how you can execute a query using a CTE from various programming languages. We’ll use the employee sales example.

Node.js (async/await)
Python (mysql-connector)
Java (JDBC)
PHP (PDO)



This example uses `mysql2/promise` to execute a query with a CTE.

```javascript
// main.js
import mysql from 'mysql2/promise';

async function getTopEmployees() {
  let connection;
  const sql = `
    WITH employee_summary AS (
      SELECT employee_id, SUM(sale_amount) AS total_sales
      FROM sales
      GROUP BY employee_id
    )
    SELECT employee_id, total_sales
    FROM employee_summary
    WHERE total_sales > ?
    ORDER BY total_sales DESC;
  `;

  try {
    connection = await mysql.createConnection({ /* connection config */ });
    const [rows] = await connection.execute(sql, [3000]);

    console.log('Top Performing Employees (Sales > 3000):');
    rows.forEach(row => {
      console.log(`  Employee ID: ${row.employee_id}, Total Sales: ${row.total_sales}`);
    });
  } finally {
    if (connection) await connection.end();
  }
}
getTopEmployees();

This Python example uses a multi-line string to define the CTE query and executes it with a parameter.

main.py

import mysql.connector

config = { 'user': 'root', 'password': 'password', 'database': 'your_database' }
sql = """
WITH employee_summary AS (
    SELECT employee_id, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY employee_id
)
SELECT employee_id, total_sales
FROM employee_summary
WHERE total_sales > %s
ORDER BY total_sales DESC
"""

with mysql.connector.connect(**config) as cnx:
    with cnx.cursor(dictionary=True) as cursor:
        cursor.execute(sql, (3000,))
        print('Top Performing Employees (Sales > 3000):')
        for row in cursor:
            print(f"  Employee ID: {row['employee_id']}, Total Sales: {row['total_sales']}")

This modern Java example executes a CTE query using a PreparedStatement.

Main.java

import java.sql.*;

public class MysqlCteExample {
    // ... connection details ...
    public static void main(String[] args) {
        String sql = """
            WITH employee_summary AS (
                SELECT employee_id, SUM(sale_amount) AS total_sales
                FROM sales
                GROUP BY employee_id
            )
            SELECT employee_id, total_sales
            FROM employee_summary
            WHERE total_sales > ?
            ORDER BY total_sales DESC
        """
;
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setBigDecimal(1, new java.math.BigDecimal("3000.00"));

            try (ResultSet rs = pstmt.executeQuery()) {
                System.out.println("Top Performing Employees (Sales > 3000):");
                while (rs.next()) {
                    System.out.printf("  Employee ID: %d, Total Sales: %.2f\n",
                        rs.getInt("employee_id"),
                        rs.getBigDecimal("total_sales"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This PHP example uses PDO to execute the CTE query.

<?php
require 'config.php'; // Assumes a file with PDO connection $pdo

$sql = "
    WITH employee_summary AS (
      SELECT employee_id, SUM(sale_amount) AS total_sales
      FROM sales
      GROUP BY employee_id
    )
    SELECT employee_id, total_sales
    FROM employee_summary
    WHERE total_sales > :min_sales
    ORDER BY total_sales DESC;
";

$stmt = $pdo->prepare($sql);
$stmt->execute(['min_sales' => 3000]);

$employees = $stmt->fetchAll();

echo "Top Performing Employees (Sales > 3000):\n";
foreach ($employees as $employee) {
    echo sprintf("  Employee ID: %d, Total Sales: %s\n",
        $employee['employee_id'],
        $employee['total_sales']
    );
}
?>