How is the usage of CTE in MySQL?

In MySQL, Common Table Expressions (CTEs) are commonly used temporary expressions that create a named temporary result set in a query for referencing in subsequent queries. CTEs can enhance the readability and performance of queries.

The general syntax for using CTE is as follows:

WITH cte_name AS (
    -- CTE的查询语句
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
-- 使用CTE
SELECT *
FROM cte_name;

In the above syntax, WITH cte_name AS is used to define a Common Table Expression (CTE), where cte_name is the name of the CTE, and the following query is used to specify the content of the CTE. Then, in subsequent queries, cte_name can be used to reference the data within the CTE.

Here is an example demonstrating how to use CTE in MySQL.

WITH employees_cte AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department_id = 10
)
SELECT *
FROM employees_cte;

In the example above, a CTE named employees_cte is firstly defined to select the IDs, names, surnames, and salaries of all employees with a department ID of 10. Then, in subsequent queries, the data in the CTE is referenced by using SELECT * FROM employees_cte.

It is important to note that in MySQL, CTE can only be used in queries after its definition in the query, so if you need to reference CTE multiple times in the same query, you can define the same CTE multiple times using the WITH keyword.