Public table expression (CTE)

Common table expressions (CTE, Common table expression) are named temporary results sets that exist only within the execution scope of a single SQL statement (such as SELECT, INSERT, UPDATE, or DELETE).

Similar to derived tables, CTEs are not stored as objects and only last during query execution; unlike derived tables, CTEs can be self-referenced or multiple references in the same query. In addition, CTE provides better readability and performance compared to derived tables.

Application Scenarios:

CTE can be used to multiplex the same subqueries in multiple places to avoid repeated writing of the same logic.
Can be used to simplify recursive queries, such as finding tree structure data.
You can split complex queries into smaller parts, making the query logic clearer and easier to understand.

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

Non-recursive public expression: refers to expressions that do not reference itself in CTE. It is only used to build a one-time temporary result set and does not involve recursive operations. The non-recursive CTE syntax is as follows:

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

Recursive common expression: refers to the expression that references itself in the CTE, which is used to process data with recursive structures, such as tree structures, graphics, etc. Recursive CTE contains a basic query (start condition) in the definition, and then performs recursive operations on the result of the basic query until the stop condition is met. The recursive CTE syntax is as follows:

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

Prepare before starting

You need to confirm that before you start, the following tasks have been completed:

Create instance has been completed.

CTE statement usage example

Suppose we want to create a table called EMPLOYEES that contains the hierarchical relationship of employees, and then query the employee hierarchy using non-recursive Common Table Expression (CTE) and recursive CTE respectively.

First, we create a EMPLOYEES table and insert some sample data:

CREATE TABLE EMPLOYEES (
    EMPLOYEE_ID INT PRIMARY KEY,
    NAME VARCHAR(50),
    MANAGER_ID INT
);

INSERT INTO EMPLOYEES (EMPLOYEE_ID, NAME, MANAGER_ID) VALUES
    (1, 'Alice', NULL),
    (2, 'Bob', 1),
    (3, 'Carol', 1),
    (4, 'David', 2),
    (5, 'Eve', 2),
    (6, 'Frank', 3),
    (7, 'Grace', 3),
    (8, 'Hannah', 4),
    (9, 'Ian', 4);

Next, we use recursive CTE to query the employee hierarchy:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EMPLOYEE_ID, NAME, MANAGER_ID, 0 AS LEVEL
    FROM EMPLOYEES
    WHERE MANAGER_ID IS NULL

    UNION ALL

    SELECT e.EMPLOYEE_ID, e.NAME, e.MANAGER_ID, eh.LEVEL + 1
    FROM EMPLOYEES e
    INNER JOIN EmployeeHierarchy eh ON e.MANAGER_ID = eh.EMPLOYEE_ID
)
SELECT * FROM EmployeeHierarchy;
+---------------------+--------------+
| employee_id | name | manager_id | level |
+---------------------+--------------+
| 1 | Alice | NULL | 0 |
| 2 | Bob | 1 | 1 |
| 3 | Carol | 1 | 1 |
| 4 | David | 2 | 2 |
| 5 | Eve | 2 | 2 |
| 6 | Frank | 3 | 2 |
| 7 | Grace | 3 | 2 |
| 8 | Hannah | 4 | 3 |
| 9 | Ian | 4 | 3 |
+---------------------+--------------+
9 rows in set (0.01 sec)

Then, we use a non-recursive CTE to query employee information:

WITH EmployeeInfo AS (
    SELECT EMPLOYEE_ID, NAME, MANAGER_ID
    FROM EMPLOYEES
)
SELECT * FROM EmployeeInfo;
+------------------------------------------------------------------------------------------------------------------------------
| employee_id | name | manager_id |
+------------------------------------------------------------------------------------------------------------------------------
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Carol | 1 |
| 4 | David | 2 |
| 5 | Eve | 2 |
| 6 | Frank | 3 |
| 7 | Grace | 3 |
| 8 | Hannah | 4 |
| 9 | Ian | 4 |
+------------------------------------------------------------------------------------------------------------------------------
9 rows in set (0.00 sec)

We used a recursive CTE EmployeeHierarchy, which first selects the top manager (MANAGER_ID IS NULL), and then finds the direct subordinates of each employee through a recursive connection while tracking the hierarchy level. This way, we can get detailed information about the employee hierarchy by querying the CTE.

The non-recursive CTE example simply selects basic information for all employees from the EMPLOYEES table, including EMPLOYEE_ID, NAME, and MANAGER_ID.

Note that recursive CTE needs to be declared using the RECURSIVE keyword.

For more documentation on using CTE, see WITH (Common Table Expressions)