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
 
Common Table Expression WITH was introduced in MariaDB
10.2.1.
 
Recursive WITH has been supported since MariaDB 10.2.2.
 
Syntax
------ 
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 also be used 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 

URL: https://mariadb.com/kb/en/with/