Common Table Expressions Overview
MariaDB starting with 10.2.1
Common table expressions were introduced in MariaDB 10.2.1.
Contents
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:
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.
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.