Here's the English translation of the provided text:

WITH (Common Table Expressions)

Syntax Explanation

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single statement, valid only during the query. It can self-reference and be referenced multiple times within the same query. Its purpose is to simplify complex queries and improve code readability and maintainability. A CTE can be thought of as a temporary view that exists only for the duration of the query's execution and is not visible to external queries.

Once defined, aCTE can be referenced like SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statements.

TheWITH clause is used to specify common table expressions. A WITH clause can use one or more comma-separated clauses. Each clause provides a subquery that generates a result set and associates a name with the subquery.

Use Cases:

CTEs can be used toreuse the same subquery in multiple places, avoiding redundant logic.
They can simplifyrecursive queries, such as finding tree-structured data.
They canbreak down complex queries into smaller parts, making the query logic clearer and easier to understand.

Common table expressions are divided into two types: non-recursive and recursive:

*Non-recursive common expression: Refers to a CTE that does not reference itself. It's only used to build a one-time temporary result set and doesn't involve recursive operations.

*Recursive common expression: Refers to a CTE that references itself, used to process data with recursive structures, such as tree structures or graphs. A recursive CTE includes a base query (starting condition) in its definition, and then performs recursive operations on the results of that base query until a stopping condition is met.

Non-Recursive CTE

Syntax Structure

WITH <query_name> AS (
    <query_definition>
)
SELECT ... FROM <query_name>;

Parameter Explanation

<query_name>: Thetemporary name specified for the CTE result set. It can be any valid identifier, similar to a table or column name.
<query_definition>: This is thequery statement that defines the CTE result set. It can be any valid SELECT query used to create the CTE's result set.
SELECT ... FROM <query_name>: This is thequery executed on the CTE, where you can use the CTE's name.

Recursive CTE

Syntax Structure

WITH RECURSIVE <query_name> AS (
    <query_definition>
)
SELECT ... FROM <query_name>;

Parameter Explanation

WITH RECURSIVE: Used toindicate that this is a recursive CTE.
<query_name>: Thetemporary name specified for the recursive CTE result set. It can be any valid identifier, similar to a table or column name.
<query_definition>: This is thequery statement that defines the recursive CTE result set. It contains two parts:

Anchor member*: Defines the starting condition and initial result set for the recursion. Recursive member*: Defines how to recursively generate the next round of results from the initial result set.
SELECT ... FROM <query_name>:Queries the recursive CTE using its name.

Recursive CTE Usage Rules

Anchor Member and Recursive Member

A recursive CTE must contain at least two query definitions: ananchor member and arecursive member. The anchor member must be before the first recursive member. Multiple anchor and recursive members can be defined. All CTE query definitions are considered anchor members unless they reference the CTE itself.

Suppose you have a table named Employee containing employee information, including EmployeeID, Name, and ManagerID fields, representing the employee's ID, name, and manager's ID. We can use a recursive CTE to query the hierarchical relationship between employees and their subordinates.

Assume the data in the table is as follows:

EmployeeID	Name	ManagerID
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2
5	Eve	2
6	Frank	3

Here's an example of using a recursive CTE to query the hierarchical relationship between employees and their subordinates:

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: Find top-level employees
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employee
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: Recursively query subordinate employees
    SELECT e.EmployeeID, e.Name, e.ManagerID, eh.Level + 1
    FROM Employee AS e
    JOIN EmployeeHierarchy AS eh ON e.ManagerID = eh.EmployeeID
)
SELECT Name, Level
FROM EmployeeHierarchy;

In the example above:

Theanchor member selects top-level employees (ManagerID is NULL) and sets their level to 0.
Therecursive member queries subordinate employees based on the previous round's results (EmployeeHierarchy) and increments the level.
The final query uses SELECT to retrieve employee names and levels from the recursive CTE.

After executing this query, you'll get information about the hierarchical relationship between employees and their subordinates, where the anchor member and recursive member together form the structure of the recursive query. Non-recursive CTEs are used to create temporary query result sets; you only need to provide one query definition and then reference this CTE in your query, without worrying about anchor and recursive members.

Operator and Statement Requirements

*Set operators: Anchor members must be combined using set operators (such as UNION ALL, UNION, INTERSECT, or EXCEPT). UNION ALL is only allowed between the last anchor member and the first recursive member, and when combining multiple recursive members.

*Column matching: The number of columns in the anchor member and recursive member must be the same.

*Data types: The data types of columns in the recursive member must match the data types of the corresponding columns in the anchor member.

*FROM clause: The FROM clause of the recursive member can only reference the CTE expression_name once.

*Unsupported features: Certain features are not allowed in the CTE_query_definition of a recursive member, including:

* Using the `SELECT DISTINCT` keyword for distinct queries.
* Using `GROUP BY` to group results.
* Using `HAVING` to filter grouped results.
* Performing scalar aggregation, i.e., applying aggregate functions (like `SUM`, `AVG`, etc.) to a group of rows to get a single value.
* Using `LEFT`, `RIGHT`, `OUTER JOIN`, or other outer join operations (however, `INNER JOIN` is allowed).
* Using subqueries.

Examples

Non-recursive CTE example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary INT
);

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 75000),
(4, 'David', 55000),
(5, 'Eve', 80000);

-- Find employees with salary higher than the average salary
mysql> WITH avg_salary AS (
       SELECT AVG(salary) AS avg_salary FROM employees)
       SELECT name, salary
       FROM employees
       JOIN avg_salary ON salary > avg_salary.avg_salary;
+---------+--------+
| name    | salary |
+---------+--------+
| Charlie |  75000 |
| Eve     |  80000 |
+---------+--------+
2 rows in set (0.00 sec)

Recursive CTE example:

CREATE TABLE employees_hierarchy (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees_hierarchy (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

-- Find an employee and all their subordinate employees
mysql> WITH RECURSIVE employee_hierarchy_cte (id, name, manager_id, level) AS (
    SELECT id, name, manager_id, 0
    FROM employees_hierarchy
    WHERE name = 'Alice'
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees_hierarchy AS e
    JOIN employee_hierarchy_cte AS eh ON e.manager_id = eh.id
)
SELECT name, level
FROM employee_hierarchy_cte;
+---------+-------+
| name    | level |
+---------+-------+
| Alice   |     0 |
| Bob     |     1 |
| Charlie |     1 |
| David   |     2 |
| Eve     |     2 |
| Frank   |     2 |
+---------+-------+
6 rows in set (0.00 sec)