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, which this article covers.
  • 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)

Comments

Comments loading...