How to Write a Common Table Expression (CTE) in MySQL

Common Table Expressions (CTEs) are a powerful feature in MySQL that allow you to simplify complex SQL queries. CTEs can be used for organizing subqueries, making your SQL more readable, and allowing for recursive queries. In this tutorial, we'll learn how to write a Common Table Expression in MySQL, explaining how it works and when to use it effectively.

What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a named temporary result set in a SQL query. CTEs are defined using the WITH keyword, and they can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve query structure and readability, especially when dealing with complex queries.

Basic Syntax

The basic syntax for a CTE in MySQL looks like this:

WITH cte_name AS (
    SQL query here
)
SELECT * FROM cte_name;
                                

Example: Simple CTE

Let's look at a simple example. Suppose we have a table named employees with columns id, name, and salary. The following query uses a CTE to retrieve employees with salaries above $50,000:

WITH high_salary_employees AS (
    SELECT id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT * FROM high_salary_employees;
                                

Why Use CTEs?

• Improved Readability: CTEs allow you to break down complex queries into smaller, more manageable parts.
• Recursion: CTEs support recursion, making them ideal for hierarchical queries (e.g., organizational charts).
• Reusability: CTEs can be referenced multiple times within a query, eliminating the need for duplicate code.

Recursive CTEs

Recursive CTEs are a special type of CTE that allow you to perform recursive queries. A recursive CTE is used to query hierarchical data, such as organizational structures or bill of materials. The recursion is defined using two parts: the anchor member and the recursive member.

Here's an example of a recursive CTE that finds all employees in a company, including their managers:

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: select the top-level employee (the CEO)
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: select employees managed by the previous level
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;
                                

Conclusion

Common Table Expressions (CTEs) are an essential tool for MySQL developers. They can greatly improve the readability and maintainability of complex queries, and they enable recursion for hierarchical data. By mastering CTEs, you'll be able to write more efficient, organized, and reusable SQL queries.