WITH (Common Table Expressions)

The WITH operation, also known as a Common Table Expression (CTE) creates a set of data that can be assigned table & column aliases and used one or more times in subsequent operations. The aliased set can be used within the SELECT, FROM, or WHERE clauses of a subsequent query or a subsequent CTE within the same WITH operation.

WITH Syntax

WITH [RECURSIVE]
	<cte definition>[,...]
<select statement>

Parameters

RECURSIVE

Allow one or more of the <cte definition> queries to be self-referential, providing for the possibility of recursive queries.

The subquery or one of the subqueries defined within this WITH clause, structured as follows:

<cte name> [(<column alias list>)] AS (<subquery>)

Parameter	Description
`<cte name>`	Table alias given to the data set returned by the CTE.
`<column alias list>`	List of aliases assigned to the columns the CTE returns.
`<subquery>`	`SELECT` statement to associate with the corresponding `<cte name>`.

Each column alias in <column alias list> is matched to each column returned by the corresponding <subquery> in the order that each were defined; e.g., for column aliases (A, B, C) used with a subquery SELECT x, y, z ..., alias A will reference column x, B will reference y, and C will reference z. If no aliases are used, the names of the source columns themselves can be used to reference the data set returned by the subquery.

The SELECT statement to run with the defined WITH subqueries.

The column aliases must be unique within the WITH statement—no other column or column alias can be similarly named, for example. Also, when used in a FROM clause and given a table alias, the table alias must be preceded with AS. A CTE can be made available to a DML or DDL statement by having the WITH statement follow the CREATE TABLE ... AS, INSERT, UPDATE, or DELETE statement (not precede it).

Examples

To define a simple CTE subquery using a WITH clause:

WITH Example

WITH
    dept2_emp_sal_by_mgr (manager_id, sal) AS
    (
        SELECT manager_id, salary
        FROM example.employee
        WHERE dept_id = 2
    )
SELECT
    manager_id dept2_mgr_id,
    MAX(sal) dept2_highest_emp_sal_per_mgr,
    COUNT(*) as dept2_total_emp_per_mgr
FROM dept2_emp_sal_by_mgr
GROUP BY manager_id

To apply the CTE to an INSERT statement while using multiple CTE definitions, follow the INSERT clause with the WITH clause:

WITH Defining Multiple Subqueries in INSERT Example

INSERT INTO example.dept2_emp_mgr_roster (emp_first_name, emp_last_name, mgr_first_name, mgr_last_name)
WITH
    dept2_emp AS
    (
        SELECT first_name, last_name, manager_id
        FROM example.employee
        WHERE dept_id = 2
    ),
    dept2_mgr AS
    (
        SELECT first_name, last_name, id
        FROM example.employee
        WHERE dept_id = 2
    )
SELECT d2emp.first_name, d2emp.last_name, d2mgr.first_name, d2mgr.last_name
FROM
    dept2_emp as d2emp
    JOIN dept2_mgr as d2mgr ON d2emp.manager_id = d2mgr.id

Recursive Queries

Common table expressions are used to create recursive queries, via the RECURSIVE option, using the following form:

Recursive WITH Syntax

WITH RECURSIVE
	<cte name> AS
	(
    	<non-recursive query>
    	UNION [ALL]
    	<recursive query>
    )
<select statement>

Use UNION to remove duplicate records from the result set; use UNION ALL to return all records from the recursion.

There is no check on queries that recurse infinitely. Ensure that recursive queries are constructed with an appropriate limit.

For instance, to query all of a given manager’s subordinates:

Recursive Query Example

WITH RECURSIVE subordinates AS
(
    SELECT
        id,
        manager_id,
        first_name,
        last_name
    FROM
        example.employee
    WHERE
        id = 6
    UNION ALL
    SELECT
        e.id,
        e.manager_id,
        e.first_name,
        e.last_name
    FROM
        example.employee e
        INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT *
FROM subordinates
ORDER BY manager_id, id

To show the management chain for each employee in a company:

Recursive Query with Recursion Trail Example

WITH RECURSIVE subordinates AS
(
    SELECT
        id,
        manager_id,
        first_name,
        last_name,
        JSON_ARRAY(first_name || ',' || last_name) AS hierarchy
    FROM
        example.employee
    WHERE
        id = 1
    UNION ALL
    SELECT
        e.id,
        e.manager_id,
        e.first_name,
        e.last_name,
        JSON_ARRAY_APPEND(s.hierarchy, e.first_name || ',' || e.last_name) AS hierarchy
    FROM
        example.employee e
        INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT *
FROM subordinates
ORDER BY manager_id, id

​Parameters

​Examples

​Recursive Queries

Parameters

Examples

Recursive Queries