Introduction to Common Table Expressions (CTEs) in MySQL

Course: MySQL Training

Topic: Introduction to Common Table Expressions (CTEs)

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a named temporary result set defined at the beginning of a SQL statement using the WITH keyword. It allows developers to divide complex queries into smaller, meaningful, and manageable sections.

In MySQL, a CTE can be referenced within the statement in which it is defined. Depending on the query, it can be used to filter records, calculate summaries, combine data from different tables, simplify subqueries, and process hierarchical relationships.

CTEs are supported in MySQL 8.0 and later. They are particularly helpful when a query contains multiple logical steps or when the same intermediate result needs to be referenced more than once within a statement.

1.1 What Is a CTE?

A CTE acts like a named query result that can be used by the main SQL statement. It is not a permanent database table. You do not need to create it separately with CREATE TABLE or remove it with DROP TABLE.

For example, a company may want to find employees earning more than ₹50,000. Instead of embedding the filtering logic inside a larger query, you can define a CTE named HighSalaryEmployees.

WITH HighSalaryEmployees AS (
    SELECT employee_id, employee_name, salary
    FROM Employees
    WHERE salary > 50000
)
SELECT *
FROM HighSalaryEmployees;

Here, HighSalaryEmployees is the CTE name. The query inside the parentheses defines its result, and the final SELECT retrieves the records from that result.

1.2 Why Are CTEs Important?

• Readability: Break complicated SQL queries into understandable steps.
• Organization: Separate data preparation from the final output.
• Reusability within a statement: Reference the same CTE multiple times in the statement where it is defined.
• Simpler maintenance: Use meaningful names for intermediate query results.
• Data analysis: Combine filtering, grouping, calculations, and joins in a structured way.
• Hierarchical queries: Use recursive CTEs to traverse organizational structures and other hierarchical data.

2. Learning Objectives

After studying this topic, learners should be able to:

• Define a Common Table Expression and explain its purpose.
• Understand the syntax of CTEs in MySQL.
• Create and execute basic CTE queries.
• Use CTEs with WHERE, ORDER BY, and aggregate functions.
• Define and use multiple CTEs in one statement.
• Combine CTEs with joins and subqueries.
• Specify custom column names for CTE results.
• Understand the basics of recursive CTEs.
• Differentiate CTEs from subqueries, views, and temporary tables.
• Identify common CTE errors and apply debugging techniques.

3. Basic Syntax of a CTE

The general syntax for creating a CTE in MySQL is:

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

The WITH clause introduces the CTE. The CTE name identifies the result set, and the query inside the parentheses defines which rows and columns it returns. The main statement uses the CTE as a source of data.

3.1 Understanding Each Syntax Component

3.2 Important Syntax Rules

• The CTE query must be enclosed in parentheses.
• The CTE definition must be followed by the SQL statement that uses it.
• Each CTE name in the same WITH clause must be unique.
• Multiple CTE definitions are separated by commas, not by writing a new WITH keyword at the same query level.
• A CTE is available only within the statement in which it is defined.
• A CTE can reference an earlier CTE in the same WITH clause.
• Use WITH RECURSIVE when a CTE refers to itself.

4. Preparing Sample Data for CTE Examples

For practical learning, create an Employees table containing employee IDs, names, department IDs, salaries, and joining dates.

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    joining_date DATE
);

INSERT INTO Employees
(employee_id, employee_name, department_id, salary, joining_date)
VALUES
(1, 'Amit Sharma', 10, 60000, '2022-01-15'),
(2, 'Neha Patel', 20, 45000, '2023-03-10'),
(3, 'Rahul Verma', 10, 75000, '2021-07-20'),
(4, 'Priya Singh', 30, 52000, '2022-11-05'),
(5, 'Karan Mehta', 20, 38000, '2024-02-18'),
(6, 'Sneha Rao', 30, 68000, '2020-09-12'),
(7, 'Vikas Jain', 10, 49000, '2023-06-25'),
(8, 'Anjali Das', 20, 82000, '2021-04-08');

Verify the records:

SELECT *
FROM Employees;

The examples below use this fictional dataset. Execute the table creation and insertion statements before running the examples that depend on this table.

5. Creating Your First CTE

Suppose you want to retrieve employees whose salaries are greater than ₹50,000. You can define a CTE to select those employees and then use the CTE in the final query.

WITH HighSalaryEmployees AS (
    SELECT employee_id, employee_name, salary
    FROM Employees
    WHERE salary > 50000
)
SELECT *
FROM HighSalaryEmployees;

5.1 Step-by-Step Explanation

1. Start with WITH: MySQL recognizes the beginning of the CTE definition.
2. Define the name: HighSalaryEmployees identifies the result set.
3. Write the inner query: The query selects employees whose salaries exceed ₹50,000.
4. Use the CTE: The final query retrieves all columns returned by the CTE.

5.2 Expected Output

The result includes only employees who satisfy the condition. The CTE gives the filtered dataset a name, making it easier to use in a more complex query.

6. Using CTEs with WHERE Conditions

CTEs can perform one filtering operation before the outer query applies additional conditions. This is useful when a query has multiple stages of filtering.

WITH DepartmentEmployees AS (
    SELECT employee_id, employee_name, department_id, salary
    FROM Employees
    WHERE department_id = 10
)
SELECT employee_name, salary
FROM DepartmentEmployees
WHERE salary > 50000;

The CTE selects employees in department 10. The outer query then returns only employees in that department earning more than ₹50,000.

6.1 Using Multiple Conditions

WITH EligibleEmployees AS (
    SELECT employee_id, employee_name, department_id, salary, joining_date
    FROM Employees
    WHERE salary >= 45000
      AND joining_date >= '2022-01-01'
)
SELECT employee_name, department_id, salary
FROM EligibleEmployees
WHERE department_id IN (10, 20)
ORDER BY salary DESC;

The inner query filters by salary and joining date. The outer query restricts the result to departments 10 and 20 and sorts the employees by salary.

7. Using CTEs with Aggregate Functions

Aggregate functions calculate summary values from multiple rows. CTEs can contain aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX().

7.1 Calculating Overall Salary Statistics

WITH SalarySummary AS (
    SELECT
        AVG(salary) AS average_salary,
        MAX(salary) AS highest_salary,
        MIN(salary) AS lowest_salary
    FROM Employees
)
SELECT *
FROM SalarySummary;

The CTE calculates the average, highest, and lowest salaries. The final query displays these statistics in one result row.

7.2 Counting Employees by Department

WITH DepartmentCounts AS (
    SELECT
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS average_salary
    FROM Employees
    GROUP BY department_id
)
SELECT department_id, employee_count, average_salary
FROM DepartmentCounts
ORDER BY employee_count DESC;

The CTE groups employees by department and calculates the employee count and average salary for each group.

7.3 Filtering Aggregated Results

WITH DepartmentSalary AS (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM Employees
    GROUP BY department_id
)
SELECT department_id, average_salary
FROM DepartmentSalary
WHERE average_salary > 50000;

The inner query calculates each department's average salary. The outer query returns only departments whose average salary exceeds ₹50,000.

8. Using Multiple CTEs in One Query

MySQL allows you to define multiple CTEs in a single WITH clause. Separate each CTE definition with a comma. This technique is useful when a report requires several processing steps.

8.1 General Syntax

WITH
FirstCTE AS (
    SELECT ...
),
SecondCTE AS (
    SELECT ...
),
ThirdCTE AS (
    SELECT ...
)
SELECT ...
FROM FirstCTE;

A CTE can refer to another CTE defined earlier in the same clause. Define dependent CTEs in the correct order.

8.2 Example: High-Salary Employees and Department Summary

WITH
HighSalaryEmployees AS (
    SELECT employee_id, employee_name, department_id, salary
    FROM Employees
    WHERE salary > 50000
),
DepartmentSummary AS (
    SELECT
        department_id,
        COUNT(*) AS high_salary_count,
        AVG(salary) AS average_high_salary
    FROM HighSalaryEmployees
    GROUP BY department_id
)
SELECT department_id, high_salary_count, average_high_salary
FROM DepartmentSummary
ORDER BY average_high_salary DESC;

Explanation:

1. HighSalaryEmployees selects employees earning more than ₹50,000.
2. DepartmentSummary uses the first CTE to calculate the number of qualifying employees and their average salary by department.
3. The final query displays the summary, sorted by average salary in descending order.

8.3 Example: Comparing Total and High-Salary Employees

WITH
EmployeeData AS (
    SELECT employee_id, employee_name, department_id, salary
    FROM Employees
),
DepartmentTotals AS (
    SELECT department_id, COUNT(*) AS total_employees
    FROM EmployeeData
    GROUP BY department_id
),
HighSalaryCounts AS (
    SELECT department_id, COUNT(*) AS high_salary_employees
    FROM EmployeeData
    WHERE salary > 50000
    GROUP BY department_id
)
SELECT
    d.department_id,
    d.total_employees,
    COALESCE(h.high_salary_employees, 0) AS high_salary_employees
FROM DepartmentTotals AS d
LEFT JOIN HighSalaryCounts AS h
    ON d.department_id = h.department_id
ORDER BY d.department_id;

This query calculates total employees and high-salary employees separately, then combines the results. COALESCE() displays zero if a department has no employees who meet the salary condition.

9. Using CTEs with JOIN Operations

A CTE can be joined with a regular database table or another CTE. This is useful when you want to filter or summarize one dataset before combining it with related information.

9.1 Create a Departments Table

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

INSERT INTO Departments (department_id, department_name)
VALUES
(10, 'IT'),
(20, 'HR'),
(30, 'Finance'),
(40, 'Marketing');

9.2 Joining a CTE with a Regular Table

WITH HighSalaryEmployees AS (
    SELECT employee_id, employee_name, department_id, salary
    FROM Employees
    WHERE salary > 50000
)
SELECT
    h.employee_id,
    h.employee_name,
    d.department_name,
    h.salary
FROM HighSalaryEmployees AS h
INNER JOIN Departments AS d
    ON h.department_id = d.department_id
ORDER BY h.salary DESC;

The CTE filters employees by salary. The outer query joins those employees with the department table to display department names.

9.3 Using LEFT JOIN to Include Every Department

WITH EmployeeCounts AS (
    SELECT department_id, COUNT(*) AS total_employees
    FROM Employees
    GROUP BY department_id
)
SELECT
    d.department_id,
    d.department_name,
    COALESCE(e.total_employees, 0) AS total_employees
FROM Departments AS d
LEFT JOIN EmployeeCounts AS e
    ON d.department_id = e.department_id
ORDER BY d.department_id;

This query returns every department, including departments without employees. Marketing appears with a count of zero because no employees in the sample dataset belong to department 40.

10. Using CTEs with Subqueries

A CTE can simplify queries that compare individual records with aggregate values. Giving the intermediate result a meaningful name can make the logic easier to follow.

10.1 Employees Earning More Than the Company Average

WITH CompanyAverage AS (
    SELECT AVG(salary) AS average_salary
    FROM Employees
)
SELECT e.employee_name, e.salary
FROM Employees AS e
CROSS JOIN CompanyAverage AS c
WHERE e.salary > c.average_salary
ORDER BY e.salary DESC;

The CTE calculates the average salary for all employees. The outer query compares every employee's salary against that average and returns those earning more.

10.2 Employees Earning More Than Their Department Average

WITH DepartmentAverage AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM Employees
    GROUP BY department_id
)
SELECT
    e.employee_name,
    e.department_id,
    e.salary,
    da.average_salary
FROM Employees AS e
INNER JOIN DepartmentAverage AS da
    ON e.department_id = da.department_id
WHERE e.salary > da.average_salary
ORDER BY e.department_id, e.salary DESC;

This query calculates each department's average salary and then returns employees whose salaries are higher than the average for their own department.

11. Specifying Column Names in a CTE

You can provide a column-name list immediately after the CTE name. The number of names must match the number of columns returned by the query inside the CTE.

WITH SalaryReport (emp_id, emp_name, annual_salary) AS (
    SELECT employee_id, employee_name, salary
    FROM Employees
)
SELECT emp_id, emp_name, annual_salary
FROM SalaryReport;

In this example, the CTE uses the column names emp_id, emp_name, and annual_salary instead of the original column names.

11.1 When Are Explicit Column Names Useful?

• When original column names are unclear or too long.
• When calculated columns need meaningful names.
• When you want a consistent naming style for the outer query.
• When defining recursive CTEs and making the result columns easier to identify.

If the specified column list contains more or fewer names than the CTE query returns columns, MySQL reports an error. Check the number and order of columns carefully.

12. Introduction to Recursive CTEs

A recursive CTE refers to its own name within its definition. It is useful for generating sequences and traversing hierarchical data, such as organizational charts, category trees, and parent-child relationships.

MySQL uses WITH RECURSIVE for recursive CTEs. A recursive CTE generally consists of two parts:

• Anchor member: Produces the initial result rows and starts the recursion.
• Recursive member: Refers to the CTE itself to produce additional rows.

These parts are combined using UNION ALL or UNION DISTINCT. A suitable termination condition is important to prevent recursion from continuing indefinitely.

12.1 Example: Generate Numbers from 1 to 10

WITH RECURSIVE NumberSeries AS (
    SELECT 1 AS number_value
    UNION ALL
    SELECT number_value + 1
    FROM NumberSeries
    WHERE number_value < 10
)
SELECT number_value
FROM NumberSeries;

The anchor query starts with 1. The recursive query adds 1 to the previous number until the stopping condition prevents another number from being generated.

12.2 Example: Traversing an Employee Hierarchy

Suppose a company stores each employee's manager in a column named manager_id.

CREATE TABLE OrganizationEmployees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT NULL
);

INSERT INTO OrganizationEmployees
(employee_id, employee_name, manager_id)
VALUES
(1, 'Meera', NULL),
(2, 'Arjun', 1),
(3, 'Kavita', 1),
(4, 'Rohan', 2),
(5, 'Isha', 3);

The following query begins with the employee who has no manager and then finds employees reporting to each person in the hierarchy.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, employee_name, manager_id, 0 AS hierarchy_level
    FROM OrganizationEmployees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id,
           eh.hierarchy_level + 1
    FROM OrganizationEmployees AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.manager_id = eh.employee_id
)
SELECT employee_id, employee_name, manager_id, hierarchy_level
FROM EmployeeHierarchy
ORDER BY hierarchy_level, employee_id;

The anchor member selects Meera. The recursive member finds employees who report to the employees already included in the result. The hierarchy_level column indicates each employee's depth from the top-level employee.

12.3 Recursive CTE Safety

• Always include a condition that eventually stops recursion.
• Check hierarchical relationships for unexpected cycles.
• Understand the MySQL recursion-depth limit and query execution limits.
• Test recursive queries on small datasets before running them on large tables.
• Inspect recursive joins carefully to avoid generating unintended rows.

13. CTEs Compared with Subqueries, Views, and Temporary Tables

Several SQL features can produce intermediate query results. Choosing the right feature depends on the complexity of the query, the need for reuse, and the lifetime of the data.

13.1 CTE vs. Subquery

A subquery is a query nested inside another SQL statement. A CTE defines an intermediate result at the beginning of the statement, allowing it to be given a descriptive name.

Subquery example:

SELECT employee_name, salary
FROM Employees
WHERE salary > (
    SELECT AVG(salary)
    FROM Employees
);

Equivalent CTE approach:

WITH CompanyAverage AS (
    SELECT AVG(salary) AS average_salary
    FROM Employees
)
SELECT e.employee_name, e.salary
FROM Employees AS e
CROSS JOIN CompanyAverage AS c
WHERE e.salary > c.average_salary;

Both queries compare employees with the company-wide average. The CTE version gives the average calculation a descriptive name and can make the logic easier to extend.

13.2 Do CTEs Always Improve Performance?

No. CTEs primarily improve query organization and readability. They do not automatically make every query faster. MySQL may merge a CTE into the outer query or materialize its result depending on the query and optimization rules. Indexes, table sizes, joins, filters, and the execution plan also affect performance.

14. Practical Business Uses of CTEs

14.1 Sales Reporting

A company may need to calculate total sales per product and identify products whose sales exceed a specified target.

WITH ProductSales AS (
    SELECT
        product_id,
        SUM(quantity * unit_price) AS total_sales
    FROM Sales
    GROUP BY product_id
)
SELECT product_id, total_sales
FROM ProductSales
WHERE total_sales > 100000
ORDER BY total_sales DESC;

This example assumes that the Sales table contains product_id, quantity, and unit_price columns.

14.2 Customer Analysis

WITH CustomerOrders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM Orders
    GROUP BY customer_id
)
SELECT customer_id, order_count
FROM CustomerOrders
WHERE order_count >= 5
ORDER BY order_count DESC;

This query identifies customers who have placed at least five orders. It can support customer segmentation, loyalty analysis, and targeted marketing.

14.3 Monthly Revenue Reporting

WITH MonthlyRevenue AS (
    SELECT
        YEAR(order_date) AS order_year,
        MONTH(order_date) AS order_month,
        SUM(order_total) AS revenue
    FROM Orders
    GROUP BY YEAR(order_date), MONTH(order_date)
)
SELECT order_year, order_month, revenue
FROM MonthlyRevenue
ORDER BY order_year, order_month;

This query calculates revenue for each year and month. It assumes the Orders table contains order_date and order_total columns.

15. Common CTE Errors and Troubleshooting

15.1 Step-by-Step Debugging

1. Run the query inside the CTE separately to verify its output.
2. Confirm that the referenced tables and columns exist.
3. Check column aliases and names used by the outer query.
4. Add joins one at a time and check for unexpected row multiplication.
5. Test each filtering condition independently.
6. For recursive CTEs, verify the anchor query, recursive join, and termination condition.
7. Use EXPLAIN to inspect the execution plan when performance is a concern.

16. Best Practices for Writing CTEs

• Choose meaningful names: Names such as MonthlyRevenue and DepartmentAverage communicate the purpose of each result.
• Keep each CTE focused: Give each CTE a clear responsibility.
• Select required columns: Avoid SELECT * in complex production queries when the required columns are known.
• Use consistent aliases: Meaningful aliases make joins and calculations easier to follow.
• Avoid unnecessary complexity: Simple queries do not always need a CTE.
• Validate intermediate results: Test each CTE before combining it with other logic.
• Order dependent CTEs correctly: A CTE can reference an earlier CTE in the same clause, not a later one.
• Check performance: Use EXPLAIN and representative data to assess execution plans.
• Protect recursive queries: Add an appropriate termination condition and understand the recursion-depth limit.
• Document complex business rules: Use short SQL comments when logic needs additional explanation.

17. Mini Project: Employee Salary Analysis Using CTEs

Project objective: Build a report that displays each department's total employee count, average salary, and count of employees earning above the company-wide average.

17.1 Project Query

WITH
CompanyAverage AS (
    SELECT AVG(salary) AS average_salary
    FROM Employees
),
DepartmentSummary AS (
    SELECT
        department_id,
        COUNT(*) AS total_employees,
        AVG(salary) AS department_average_salary
    FROM Employees
    GROUP BY department_id
),
AboveCompanyAverage AS (
    SELECT
        department_id,
        COUNT(*) AS employees_above_company_average
    FROM Employees
    WHERE salary > (
        SELECT average_salary
        FROM CompanyAverage
    )
    GROUP BY department_id
)
SELECT
    d.department_id,
    d.total_employees,
    ROUND(d.department_average_salary, 2) AS department_average_salary,
    COALESCE(a.employees_above_company_average, 0)
        AS employees_above_company_average
FROM DepartmentSummary AS d
LEFT JOIN AboveCompanyAverage AS a
    ON d.department_id = a.department_id
ORDER BY d.department_id;

17.2 Project Explanation

1. CompanyAverage calculates the average salary across all employees.
2. DepartmentSummary calculates the employee count and average salary for each department.
3. AboveCompanyAverage counts employees whose salaries exceed the company average.
4. The final query combines the department statistics and above-average employee counts.
5. COALESCE() displays zero when a department has no employees earning above the company average.

17.3 Project Extensions

• Display only departments with an average salary above a specified threshold.
• Add the highest and lowest salary for each department.
• Join the result with the Departments table to display department names.
• Include employees who joined during a selected date range.
• Compare each department's average salary with the company-wide average.
• Use EXPLAIN to inspect the query's execution plan.

18. Practice Exercises

1. Create a CTE named RecentEmployees to retrieve employees who joined after January 1, 2023.
2. Create a CTE that returns employees earning less than ₹50,000.
3. Use a CTE to calculate the average salary for each department.
4. Return departments whose employee count is greater than two.
5. Create two CTEs, one for employees and another for department statistics, and join their results.
6. Use a CTE to identify employees earning more than the company-wide average salary.
7. Write a CTE with an explicit column-name list.
8. Create a recursive CTE that generates numbers from 1 to 20.
9. Use a CTE to summarize monthly sales from a sample sales table.
10. Investigate a CTE query using EXPLAIN and describe its execution plan.

19. Interview Questions and Answers

Q1. What is a Common Table Expression in MySQL?

A CTE is a named temporary result set defined using the WITH clause. It is available within the SQL statement in which it is defined.

Q2. Which keyword is used to define a CTE?

The WITH keyword defines a regular CTE. Recursive CTEs use WITH RECURSIVE.

Q3. Which MySQL versions support CTEs?

CTEs were introduced in MySQL 8.0. MySQL 8.0 or later is required for the examples in these notes.

Q4. Is a CTE a permanent database table?

No. A CTE is scoped to one SQL statement and does not create a permanent database table.

Q5. Can one query contain multiple CTEs?

Yes. Multiple CTEs can be defined in one WITH clause, separated by commas. Each name must be unique within that clause.

Q6. Can one CTE reference another CTE?

Yes. A CTE can reference another CTE defined earlier in the same WITH clause.

Q7. What is the difference between a regular CTE and a recursive CTE?

A regular CTE does not refer to itself. A recursive CTE references its own name and can be used for sequences and hierarchical data.

Q8. What is an anchor member?

The anchor member produces the initial rows from which a recursive CTE begins.

Q9. Do CTEs always improve query performance?

No. CTEs can improve readability, but performance depends on the query, data, indexes, and MySQL's optimization decisions.

Q10. Can CTEs be used with JOIN operations?

Yes. CTEs can be joined with regular tables or other CTEs using standard SQL join operations.

Q11. What happens if a CTE is referenced outside its statement?

The reference fails because the CTE is only available within the statement that defines it.

Q12. Why is a termination condition important in a recursive CTE?

A termination condition stops recursion from continuing indefinitely. Without a suitable stopping condition, the query may reach MySQL's recursion-depth or execution-time limit.

20. Quick Revision Checklist

• A CTE is a named temporary result set.
• Use WITH to define a regular CTE.
• Use WITH RECURSIVE for recursive CTEs.
• Enclose the CTE query in parentheses after AS.
• Use the CTE in the SQL statement that defines it.
• Separate multiple CTE definitions with commas.
• Use explicit column names when they improve clarity.
• Combine CTEs with filtering, aggregation, joins, and subqueries.
• Use recursive CTEs for sequences and hierarchies, with a safe stopping condition.
• Remember that CTEs do not automatically improve query performance.
• Use EXPLAIN and test intermediate results when troubleshooting.

21. Conclusion

Common Table Expressions are an important MySQL feature for organizing and simplifying SQL queries. By defining named intermediate result sets, developers can separate complex logic into understandable steps and create reports involving filtering, aggregation, joins, and subqueries.

Regular CTEs help structure queries, while recursive CTEs support tasks such as generating number sequences and traversing hierarchical data. To use CTEs effectively, practice the syntax, validate intermediate results, choose meaningful names, and review execution plans when performance matters.

To learn more about MySQL and its practical applications, explore the MySQL Training Course and register for a course demo.

Official Reference: MySQL Reference Manual – WITH (Common Table Expressions).