What is CTE in MySQL 8: Explanation and Examples

In MySQL 8, a CTE (Common Table Expression) is a temporary named result set that you can reference within a single query. It helps organize complex queries by breaking them into simpler parts, improving readability and reusability.

How It Works

A Common Table Expression (CTE) in MySQL 8 works like a temporary table that exists only during the execution of a single query. Think of it as a way to create a mini-table inside your query that you can refer to multiple times. This makes your SQL easier to read and maintain, especially when dealing with complex joins or recursive data.

Imagine you are cooking a meal and you prepare some ingredients in advance, like chopping vegetables or marinating meat. These prepared ingredients are like the CTE — a ready-to-use part that simplifies the final cooking steps. Similarly, a CTE prepares a subset of data that you can use in the main query without repeating the same code.

Example

This example shows a simple CTE that selects employees with a salary above 50000 and then uses that CTE to get their names and salaries.

sql

WITH HighEarners AS (
  SELECT employee_id, name, salary
  FROM employees
  WHERE salary > 50000
)
SELECT name, salary FROM HighEarners;

Output

name  | salary
------|--------
Alice | 60000
Bob   | 75000

When to Use

Use CTEs when you want to simplify complex queries by breaking them into smaller, understandable parts. They are especially helpful for:

• Improving query readability by naming intermediate results.
• Reusing the same subquery multiple times within a query.
• Writing recursive queries, such as traversing hierarchical data like organizational charts or folder structures.

For example, if you need to calculate totals from a filtered set of data and then use those totals in further calculations, a CTE can make your SQL clearer and easier to debug.

Key Points

• A CTE is defined using the WITH keyword followed by a name and a query.
• CTEs exist only during the execution of the query and do not create permanent tables.
• They improve query organization and can be recursive.
• MySQL 8 introduced support for CTEs, making complex queries easier to write.

Key Takeaways

CTEs in MySQL 8 create temporary named result sets to simplify complex queries.

They improve readability and allow reuse of subqueries within a single query.

CTEs are useful for recursive queries and hierarchical data processing.

Defined with the WITH keyword, CTEs do not persist beyond the query execution.