SQL-99 added the common table expression, or CTE. It is also a query expression that is given a name, just like a derived table. The difference is that they appear before the SELECT statement to which they belong.
The syntax is fairly straightforward for the simple CTE.
WITH <cte list> <select stmt>; <cte list> ::= <cte expr> [, <cte expr>] .. <cte exp> ::= <cte name> [(<column name list>)] AS (<select stmt>)
The query or other statement begins with the keyword WITH, followed by a comma-separated list of CTEs. Each CTE has a unique name within the statement, an optional list of column names, and the keyword AS (it is required) followed by the defining query. In short, the same elements we ...