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