MariaDB starting with 10.2.1

Common table expressions were introduced in MariaDB 10.2.1.

Common Table Expressions (CTEs) are a standard SQL feature, and are essentially temporary named result sets. There are two kinds of CTEs:

  • Non-recursive
  • Recursive

Non-recursive CTEs

The WITH keyword signifies a CTE. It is given a name, followed by a body (the main query) as follows: cte_syntax

CTEs are similar to derived tables. For example

with engineers as (
select *
from employees
where dept='Engineering'
)
select *
from engineers
where ...
select *
from
(
select *
from employees
where
dept='Engineering'
) as engineers
where
...
  • Non-recursive CTES are basically 'query-local VIEWs'.
  • One CTE can refer to another.
  • The syntax is more readable than nested FROM (SELECT ...).
  • One can refer to a CTE from multiple places.
  • They are better than copy-pasting FROM(SELECT ...)

Use case 1: CTEs refer to CTEs

Using this format makes for more readable SQL than a nested FROM(SELECT ...)

with engineers as (
select * from employees
where dept in ('Development','Support')
),
eu_engineers as (
select * from engineers where country IN ('NL',...)
)
select
...
from
eu_engineers;

Use case #2: Multiple use of CTE

This can be an 'anti-self join', for example:

with engineers as (
select * from employees
where dept in ('Development','Support')
),
select *
from
engineers E1
where not exists (select 1
from engineers E2
where E2.country=E1.country
and E2.name <> E1.name);

Or, for year-over-year comparisons, for example:

with sales_product_year as (
select
product,
year(ship_date) as year,
sum(price) as total_amt
from
item_sales
group by
product, year
)

select *
from
sales_product_year CUR,
sales_product_year PREV,
where
CUR.product=PREV.product and
CUR.year=PREV.year + 1 and
CUR.total_amt > PREV.total_amt

Another use is to compare individuals against their group, for example:

with sales_product_year as (
select
product,
year(ship_date) as year,
sum(price) as total_amt
from
item_sales
group by
product, year
)

select *
from
sales_product_year S1
where
total_amt > (select
0.1*sum(total_amt)
from
sales_product_year S2
where
S2.year=S1.year)

Recursive CTEs

SQL is generally poor at recursive structures.

trees_and_graphs

Oracle first attempted something similar in the 1980's with the CONNECT BY syntax. They appeared in the SQL standard in 1999, and the first implementations began appearing in 2007.

Syntax

recursive_cte_syntax

Comments

Comments loading...
Loading