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.