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.
with_clause : WITH common_table_expression [, common_table_expression ...] common_table_expression : [RECURSIVE] table_name [(column_name [, column_name ...])] AS (select_query)
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.
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 | +---+ */