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

  1. WITH cte_name ( column_name [,...n] )
  2. AS
  3. (
  4.     CTE_query_definition –- Anchor member is defined.
  5. )

Explanations

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

Examples

  1. CREATE TABLE t1
  2.     (a INTEGER,
  3.     b INTEGER,
  4.     c INTEGER
  5.     );
  6. INSERT INTO t1 VALUES
  7.     (1, 1, 10), (1, 2, 20), (1, 3, 30), (2, 1, 40), (2, 2, 50), (2, 3, 60);
  8. CREATE TABLE t2
  9.     (a INTEGER,
  10.     d INTEGER,
  11.     e INTEGER
  12.     );
  13. INSERT INTO t2 VALUES
  14.     (1, 6, 60), (2, 6, 60), (3, 6, 60);
  15. mysql> WITH
  16.     cte AS
  17.     (SELECT SUM(c) AS c, SUM(b) AS b, a
  18.     FROM t1
  19.     GROUP BY a)
  20.     SELECT t2.a, (SELECT MIN(c) FROM cte AS cte2 WHERE t2.d = cte2.b)
  21.     FROM t2 LEFT JOIN cte AS cte1 ON t2.a = cte1.a
  22.     LEFT JOIN t2 AS tx ON tx.e = cte1.c;
  23. +------+------------------------------------------------------+
  24. | a    | (select min(c) from cte as cte2 where t2.d = cte2.b) |
  25. +------+------------------------------------------------------+
  26. |    1 |                                                   60 |
  27. |    1 |                                                   60 |
  28. |    1 |                                                   60 |
  29. |    2 |                                                   60 |
  30. |    3 |                                                   60 |
  31. +------+------------------------------------------------------+
  32. 5 rows in set (0.01 sec)

Constraints

MatrixOne doesn’t support recursive CTE yet.