Description\n-----------\n\nCommon 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.\n\nNon-Recursive CTEs\n\nThe WITH keyword signifies a CTE. It is given a name, followed by a body (the main query).\n\nCTEs are similar to derived tables:\n\n``sql\nWITH engineers AS \n   ( SELECT  FROM employees\n     WHERE dept = 'Engineering' )\n\nSELECT  FROM engineers\nWHERE ...\n`\n\n`sql\nSELECT  FROM\n   ( SELECT  FROM employees\n     WHERE dept = 'Engineering' ) AS engineers\nWHERE\n...\n`\n\nA non-recursive CTE is basically a query-local VIEW. There are several advantages and caveats to them. The syntax is more readable than a nested FROM (SELECT ...).\\\nA CTE can refer to another and it can be referenced from multiple places.\n\nA CTE referencing Another CTE\n\nUsing this format makes for a more readable SQL than a nested FROM(SELECT ...) clause:\n\n`sql\nWITH engineers AS (\nSELECT  FROM employees\nWHERE dept IN('Development','Support') ),\neu_engineers AS ( SELECT  FROM engineers WHERE country IN('NL',...) )\nSELECT\n...\nFROM eu_engineers;\n`\n\nMultiple Uses of a CTE\n\nThis can be an 'anti-self join', for example:\n\n`sql\nWITH engineers AS (\nSELECT  FROM employees\nWHERE dept IN('Development','Support') )\n\nSELECT  FROM engineers E1\nWHERE NOT EXISTS\n   (SELECT 1 FROM engineers E2\n    WHERE E2.country=E1.country\n    AND E2.name <> E1.name );\n`\n\nOr, for year-over-year comparisons, for example:\n\n`sql\nWITH sales_product_year AS (\nSELECT product, YEAR(ship_date) AS year,\nSUM(price) AS total_amt\nFROM item_sales\nGROUP BY product, year )\n\nSELECT \nFROM sales_product_year CUR,\nsales_product_year PREV,\nWHERE CUR.product=PREV.product \nAND  CUR.year=PREV.year + 1 \nAND CUR.total_amt > PREV.total_amt\n`\n\nAnother use is to compare individuals against their group. Below is an example of how this might be executed:\n\n`sql\nWITH sales_product_year AS (\nSELECT product,\nYEAR(ship_date) AS year,\nSUM(price) AS total_amt\nFROM item_sales\nGROUP BY product, year\n)\n\nSELECT  \nFROM sales_product_year S1\nWHERE\ntotal_amt > \n    (SELECT 0.1 * SUM(total_amt)\n     FROM sales_product_year S2\n     WHERE S2.year = S1.year)\n``\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/non-recursive-common-table-expressions-overview