WITH

A Common Table Expression (CTE) is a temporary result set that can be referred multiple times within a SQL statement to improve the statement’s readability and execution efficiency. You can apply the WITH statement to use Common Table Expressions.

Synopsis

WithClause:

WithClause

WITH - 图1

  1. WithClause ::=
  2.         "WITH" WithList
  3. |       "WITH" "RECURSIVE" WithList

WithList:

WithList

WITH - 图2

  1. WithList ::=
  2.         WithList ',' CommonTableExpr
  3. |       CommonTableExpr

CommonTableExpr:

CommonTableExpr

WITH - 图3

  1. CommonTableExpr ::=
  2.         Identifier IdentListWithParenOpt "AS" SubSelect

IdentListWithParenOpt:

IdentListWithParenOpt

WITH - 图4

  1. IdentListWithParenOpt ::=
  2. ( '(' IdentList ')' )?

Examples

Non-recursive CTE:

  1. WITH cte AS (SELECT 1, 2) SELECT * FROM cte t1, cte t2;
  1. +---+---+---+---+
  2. | 1 | 2 | 1 | 2 |
  3. +---+---+---+---+
  4. | 1 | 2 | 1 | 2 |
  5. +---+---+---+---+
  6. 1 row in set (0.00 sec)

Recursive CTE:

  1. WITH RECURSIVE cte(a) AS (SELECT 1 UNION SELECT a+1 FROM cte WHERE a < 5) SELECT * FROM cte;
  1. +---+
  2. | a |
  3. +---+
  4. | 1 |
  5. | 2 |
  6. | 3 |
  7. | 4 |
  8. | 5 |
  9. +---+
  10. 5 rows in set (0.00 sec)

MySQL compatibility

  • In strict mode, when the data length recursively calculated exceeds the data length of the seed part, TiDB returns a warning while MySQL returns an error. In non-strict mode, the behavior of TiDB is consistent with that of MySQL.
  • The data type for recursive CTE is determined by the seed part. The data type of the seed part is not completely consistent with MySQL in some cases (such as functions).
  • In the case of multiple UNION / UNION ALL operators, MySQL does not allow UNION to be followed by UNION ALL, but TiDB does.
  • If there is a problem with the definition of a CTE, TiDB will report an error, while MySQL will not if the CTE is not referred.

See also