An Introduction to MySQL CTE

An Introduction to MySQL CTE (Common Table Expressions)

A CTE (Common Table Expression) in MySQL is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE query. It provides an easier way to manage complex queries by breaking them into smaller, readable parts. Introduced in MySQL 8.0, CTEs are especially useful for recursive queries and improving code readability.

Key Features of CTEs

1. Readability:
   • Makes complex queries easier to write and understand.
2. Reusability:
   • The CTE can be referenced multiple times in the same query.
3. Temporary Nature:
   • Exists only during the execution of the query.

CTE Syntax

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

• WITH cte_name AS:
  • Defines the name of the CTE.
• SELECT:
  • Contains the query that generates the temporary result set.
• Referencing the CTE:
  • Use cte_name in the main query.

Examples

Example 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  |
+----+----------+------------+-------+

1. Simple CTE

Find all employees in the IT department:

WITH IT_Employees AS (
    SELECT name, salary
    FROM employees
    WHERE department = 'IT'
)
SELECT * 
FROM IT_Employees;

Result

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

2. Using Multiple CTEs

Calculate the average salary by department and filter departments with an average salary above $5,000:

WITH DepartmentSalary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
HighSalaryDepartments AS (
    SELECT department
    FROM DepartmentSalary
    WHERE avg_salary > 5000
)
SELECT name, department
FROM employees
WHERE department IN (SELECT department FROM HighSalaryDepartments);

Result

+----------+------------+
| name     | department |
+----------+------------+
| Alice    | IT         |
| Charlie  | IT         |
+----------+------------+

---

3. Recursive CTE

Find the sum of all salaries using a recursive CTE:

WITH RECURSIVE SalarySum AS (
    SELECT salary AS total, id
    FROM employees
    WHERE id = 1
    UNION ALL
    SELECT s.total + e.salary, e.id
    FROM SalarySum s
    JOIN employees e ON e.id = s.id + 1
)
SELECT MAX(total) AS total_salary
FROM SalarySum;

Result

+--------------+
| total_salary |
+--------------+
| 25000        |
+--------------+

4. CTE in Data Modification

Insert new employees into the employees table from a CTE:

WITH NewEmployees AS (
    SELECT 'Frank' AS name, 'Marketing' AS department, 4500 AS salary
    UNION ALL
    SELECT 'Grace', 'Marketing', 4700
)
INSERT INTO employees (name, department, salary)
SELECT name, department, salary
FROM NewEmployees;

5. Joining a CTE

Find the department with the highest-paid employee:

WITH MaxSalary AS (
    SELECT department, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary
FROM employees e
JOIN MaxSalary m ON e.department = m.department AND e.salary = m.max_salary;

Result

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

Advantages of Using CTEs

1. Improves Query Structure:
   • Break down queries into manageable parts.
2. Simplifies Recursion:
   • Handles hierarchical or iterative queries with RECURSIVE.
3. Reusable:
   • Eliminates the need to repeat complex subqueries.
4. Readable:
   • Makes the SQL easier to maintain and debug.

Limitations of CTEs

1. Performance:
   • For large datasets, CTEs may perform worse than temporary tables or derived tables.
2. Limited Scope:
   • Cannot persist across queries; exists only during the execution of the current query.

CTEs vs. Derived Tables

Feature	CTEs	Derived Tables
Reusability	Can be referenced multiple times	Can only be used once
Readability	More readable	Harder to follow in complex queries
Recursion	Supports recursion	Does not support recursion
Scope	Query-wide	Query-specific

Use Cases

1. Hierarchical Data:
   • E.g., Employee-manager relationships.
2. Simplifying Aggregations:
   • Pre-aggregate data for easier final computations.
3. Complex Joins:
   • Use CTEs to prefilter or transform data for joining.
4. Recursive Queries:
   • Handle data with a tree or graph structure.

Performance Tips

1. Test Query Execution:
   • Use EXPLAIN to analyze the execution plan of queries with CTEs.
2. Avoid Large Temporary Results:
   • Filter data in the CTE to reduce unnecessary processing.
3. Consider Alternatives:
   • For repetitive queries, consider temporary tables.

Let me know if you need further examples or a detailed explanation!