WITH
MariaDB starting with 10.2.1
Common Table Expression WITH
was introduced in MariaDB 10.2.1.
Syntax
WITH [RECURSIVE] table_reference AS ( SELECT ... ) [CYCLE cycle_column_list RESTRICT] SELECT ...
Contents
Description
The WITH
keyword signifies a Common Table Expression (CTE). It allows you to refer to a subquery expression many times in a query, as if having a temporary table that only exists for the duration of a query.
There are two kinds of CTEs:
- Non-Recursive
- Recursive (signified by the
RECURSIVE
keyword, supported since MariaDB 10.2.2)
You can use table_reference
as any normal table in the external SELECT
part. You can also use WITH
in subqueries, as well as with EXPLAIN and SELECT.
Below is an example with the WITH
at the top level:
WITH t AS (SELECT a FROM t1 WHERE b >= 'c') SELECT * FROM t2, t WHERE t2.c = t.a;
The example below uses WITH
in a subquery:
SELECT t1.a, t1.b FROM t1, t2 WHERE t1.a > t2.c AND t2.c IN(WITH t AS (SELECT * FROM t1 WHERE t1.a < 5) SELECT t2.c FROM t2, t WHERE t2.c = t.a);
Below is an example of a Recursive CTE:
WITH RECURSIVE ancestors AS ( SELECT * FROM folks WHERE name="Alex" UNION SELECT f.* FROM folks AS f, ancestors AS a WHERE f.id = a.father OR f.id = a.mother ) SELECT * FROM ancestors;
Poorly-formed recursive CTEs can in theory cause infinite loops. The max_recursive_iterations system variable limits the number of recursions.
CYCLE ... RESTRICT
MariaDB starting with 10.5.2
MariaDB supports a relaxed, non-standard grammar.
The SQL Standard permits a CYCLE clause, as follows:
WITH RECURSIVE ... ( ... ) CYCLE <cycle column list> RESTRICT SET <cycle mark column> TO <cycle mark value> DEFAULT <non-cycle mark value> USING <path column>
where all clauses are mandatory.
MariaDB does not support this, but from 10.5.2 permits a non-standard relaxed grammar, as follows:
WITH RECURSIVE ... ( ... ) CYCLE <cycle column list> RESTRICT