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 was supported since MariaDB 10.2.2.
WITH is a Common table expression (CTE)
WITH allows you to refer to a subquery expression many times in a query. It's like having a temporary table that only exists for the duration of a query.
WITH 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 on the top level:
WITH t as (select a from t1 where b >= 'c') select * from t2,t where t2.c=t.a;
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);