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;
and Recursive.
 
Common table expressions were introduced in MariaDB 10.2.1.
 
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
...
 
A non-recursive CTE is basically a query-local VIEW. There
are several advantages and caveats to them. The syntax is
more readable than nested FROM (SELECT ...).
A CTE can refer to another and it can be referenced from
multiple places.
 
A CTE referencing Another CTE
 
Using this format makes for a more readable SQL than a
nested FROM(SELECT ...) clause. Below is an example of this:
 
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;
 
Multiple Uses of a 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.
Below is an example of how this might be executed:
 
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)
 

 
 
 
 
 
 
 

URL:
https://mariadb.com/kb/en/non-recursive-common-table-expressions-overview/