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:
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.