Non-Recursive Common Table Expressions Overview

You are viewing an old version of this article. View the current version here.
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...
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.