WITH (Common Table Expressions)

Description

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.

To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each sub-clause provides a subquery that produces a result set, and associates a name with the subquery.

Syntax

WITH cte_name ( column_name [,...n] )
AS
(
    CTE_query_definition –- Anchor member is defined.
)

Explanations

In the statement containing the WITH clause, each CTE name can be referenced to access the corresponding CTE result set.

Examples

CREATE TABLE t1
    (a INTEGER,
    b INTEGER,
    c INTEGER
    );
INSERT INTO t1 VALUES
    (1, 1, 10), (1, 2, 20), (1, 3, 30), (2, 1, 40), (2, 2, 50), (2, 3, 60);
CREATE TABLE t2
    (a INTEGER,
    d INTEGER,
    e INTEGER
    );
INSERT INTO t2 VALUES
    (1, 6, 60), (2, 6, 60), (3, 6, 60);
mysql> WITH
    cte AS
    (SELECT SUM(c) AS c, SUM(b) AS b, a
    FROM t1
    GROUP BY a)
    SELECT t2.a, (SELECT MIN(c) FROM cte AS cte2 WHERE t2.d = cte2.b)
    FROM t2 LEFT JOIN cte AS cte1 ON t2.a = cte1.a
    LEFT JOIN t2 AS tx ON tx.e = cte1.c;
+------+------------------------------------------------------+
| a    | (select min(c) from cte as cte2 where t2.d = cte2.b) |
+------+------------------------------------------------------+
|    1 |                                                   60 |
|    1 |                                                   60 |
|    1 |                                                   60 |
|    2 |                                                   60 |
|    3 |                                                   60 |
+------+------------------------------------------------------+
5 rows in set (0.01 sec)

Constraints

MatrixOne doesn't support recursive CTE yet.