The Ultimate Guide to High Availability with MariaDB

WITH

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.2.1

The Common table expression WITH was introduced in MariaDB 10.2.1.

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.

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

TODO

We are working on extending WITH for recursive 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.