SQL Common Table Expression

16 Apr 2016

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 WITH clause, 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.