WITH
You are viewing an old version of this article. View
the current version here.
WITH
is a Common table expression (CTE)
WITH
allows you to refer to a sub query expression many times in a query. It's like having a temporary table that only exist for the duration of a query.
WITH
is supported starting from MariaDB 10.2.1
Syntax
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
can be used with EXPLAIN and SELECT.
Examples
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 sub query:
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);
TODO
We are working on extending WITH
for sub queries. MDEV-9864.
Comments
Comments loading...
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.