MariaDB starting with 10.2.1

The Common table expression WITH was introduced in MariaDB 10.2.1.

MariaDB starting with 10.2.2

Recursive WITH has been supported since MariaDB 10.2.2.

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:


WITH [RECURSIVE] table_reference as (SELECT ...)
  SELECT ...

You can use table_reference as any normal table in the external SELECT part. You can also use WITH in sub queries.

WITH can be used with EXPLAIN and SELECT.


Using WITH on the top level:

WITH t as (select a from t1 where b >= 'c') 
  select * from t2,t where t2.c=t.a;

Using 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);

Recursive CTE:

  SELECT * FROM folks
  WHERE name="Alex"
  SELECT f.*
  FROM folks AS f, ancestors AS a
  WHERE = a.father OR = a.mother
SELECT * FROM ancestors;

See also


Comments loading...