Common Table Expressions (CTEs)

Common Table Expressions (CTEs), also known as queries, take advantage of a particular syntax when writing sub-queries that make statements a lot more readable. Readability, however, is not the only reason to use CTEs: their structure also allows for a statement pipeline of sorts, and in their recursive version, they can be used to loop through joins.

Introducing CTEs

Any DML statement (such as SELECT, INSERT, UPDATE, and DELETE) can be enhanced by placing a WITH predicate before the query. Any sub-query appearing in the WITH clause can be thought of as a materialized result set over the data, or as a temporary table that exists throughout the duration of a statement. CTEs have many applications...