Using Common Table Expressions (CTEs) in MySQL 8

CTEs are a feature introduced by MySQL 8.0 to improve the readability and maintenance of complex queries. 1. CTE is a temporary result set, which is only valid in the current query, has a clear structure, and supports duplicate references; 2. Compared with subqueries, CTE is more readable, reusable and supports recursion; 3. Recursive CTE can process hierarchical data, such as organizational structure, which needs to include initial query and recursion parts; 4. Use suggestions include avoiding abuse, naming specifications, focusing on performance and debugging methods.

CTEs (Common Table Expressions) is a very practical feature introduced by MySQL 8.0. It makes complex queries clearer and more readable. If you have written SQL with nested subqueries or multi-layer derived tables before, you will find that the structure is refreshing and easier to maintain after using CTE.

What is CTE?

CTE is a temporary result set that you can define in a query and reference it in subsequent queries like using a normal table. It only exists during the current query execution and will not be persisted into the database.

Let’s give a simple example: you want to find out the employees with the highest salary in each department. If you do not use CTE, you may have to write multiple subqueries to nest them, which are logically circumvented. If you use CTE, you can first use the "maximum salary for each department" as an intermediate result, and then associate the employee table to filter the data.

 WITH MaxSalaries AS (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN MaxSalaries ms
ON e.department_id = ms.department_id AND e.salary = ms.max_salary;

Does this look more intuitive? And you can also define multiple CTEs in one query and use them in combination on demand.

Basic syntax of CTE

The structure of CTE is very clear, starting with WITH , then the name you gave to this temporary result set, followed by the query statement in brackets. After that, you can use this name repeatedly within this query scope.

The format is as follows:

 WITH cte_name [(column_list)] AS (
    -- Query statement)
-- The main query that uses CTE in the subsequent

A few notes:

• CTE can have column names, and if not specified, it will be automatically retrieved from the query by default.
• If there are multiple CTEs, they can be separated by commas and defined in sequence.
• CTE is only valid in the current query and cannot be reused across queries.

The difference between CTE and subquery

Many people will ask: What is the difference between CTE and sub-query? In fact, they are very similar in functionality, but CTE has more advantages in readability and reusability.

For example, if you want to use "selling summary for a certain month" in multiple places, you have to write it several times with subqueries, and you can call it multiple times with CTE as long as you define it once.

Use recursive CTE to process hierarchical data

MySQL 8.0 also supports recursive CTE , which is very suitable for processing tree structures or hierarchical data, such as organizational structures, classification directories, comment reply chains, etc.

Suppose you have an employees table that contains the employee ID and the superior manager ID. If you want to find all the subordinates under a certain leader, then the recursive CTE will come in handy.

The sample code is as follows:

 WITH RECURSIVE Subordinates AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id = 100 -- Initial condition: Find the direct subordinate UNION ALL

    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM Subordinates;

This code first selects direct subordinates, and then constantly recursively finds subordinates of these people until there are no more records. The core of recursive CTE is two parts:

• Initial query (non-recursive part)
• Recursive part, connected via UNION ALL

It should be noted that recursion cannot continue indefinitely, otherwise an error will be reported. You can control the maximum recursion depth by setting cte_max_recursion_depth .

Practical application suggestions

CTE is powerful, but there are some tips and precautions when using it:

• Don't abuse CTE : Not all scenarios are suitable for using CTE. There is no need to split it into CTE in simple queries, which will increase complexity.
• Naming specification : Give the CTE a meaningful name, such as TopCustomers or RecentOrders , to help others understand your logic.
• Performance issues : CTE does not necessarily improve performance, it is more like a logical package. If you are sensitive to performance, it is best to analyze it in combination with index and execution plan.
• Debugging method : Want to see the intermediate results of CTE? You can directly write CTE SELECT * FROM cte_name for easy debugging.

Basically that's it. CTE is a very practical part of modern SQL, especially when writing complex queries, which can help you clarify your ideas and improve readability. You may not be used to it at first, but once you get started, you will find that the experience of writing SQL is really different.

The above is the detailed content of Using Common Table Expressions (CTEs) in MySQL 8. For more information, please follow other related articles on the PHP Chinese website!