Common Table Expression

A Common Table Expression in a with clause declare an inline table that can be referenced in a single query. You can use the views in a Select Query, Insert Query, Update Query, or Delete Query.

Syntax

with_clause
  : WITH common_table_expression [, common_table_expression ...]

common_table_expression
  : [RECURSIVE] table_name [(column_name [, column_name ...])] AS (select_query)

table_name

identifier

column_name

identifier

select_query

select_query

Recursion

If you specified a RECURSIVE keyword, the select_query in the common_table_clause can retrieve the result recursively. A RECURSIVE keyword is usually used with a UNION operator.

WITH
  RECURSIVE table_name [(column_name [, column_name ...])]
  AS (
    base_select_query
    UNION [ALL]
    recursive_select_query
  )

At first, the result set of the base_select_query is stored in the temporary view for recursion. Next, the recursive_select_query that refer to the temporary view is excuted and the temporary view is replaced by the result set of the recursive_select_query. The execution of the recursive_select_query is iterated until the result set is empty. All the result sets are combined by the UNION operator.

Example:

WITH RECURSIVE t (n)
  AS (
    SELECT 1
    UNION ALL
    SELECT n + 1
      FROM t
     WHERE n < 5
  )
SELECT n FROM t;


/* Result Set
+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
*/