Common Table Expression (CTE) is a useful standard SQL construct which allows you to create temporary named result set(s) exists just for one query. It is useful because it breaks down complex queries into simpler parts, for example, a CTE query understood by PostgreSQL:
WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;
Quite straight-forward, isn’t it? The
WITH clause part is the Common Table Expression. Without the
the above example would have been written with two levels of nested sub-
SELECTs; therefore, CTE refactored a complex
query into a more readible one.
Not only is CTE useful, but also powerful because of an optional
RECURSIVE modifier inside the
WITH clause. This
modifier makes writing loops in SQL possible! A recursive CTE is often used to deal with hierarchical or tree-structured
data: walk a tree or traverse a graph. Here is a simple example of an CTE recognized by SQLite:
/* simple loop from 1 to 999999 */ WITH RECURSIVE cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000) SELECT x FROM cnt;
For more involved recursive CTE, please check out PostgreSQL CTE Documentation, SQLite CTE Documentation or my own CTE examples. This powerful addition to the SQL syntax makes solving complex problems entirely in SQL possible; for example, like solving SUDOKU.