WITH (Common Table Expressions) WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT b, d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c; In the statement containing the  WITH  clause, each CTE name can be referenced to access the corresponding CTE result set. A CTE name can be referenced in other CTEs, enabling CTEs to be defined based on other CTEs. A CTE can refer to itself to define a recursive CTE. Common applications of recursive CTEs include series generation and traversal of hierarchical or tree-structured data. Common table expressions are an optional part of the syntax for DML statements. They are defined using a  WITH  clause: with_clause : WITH [RECURSIVE] cte_name  [( col_name  [,  col_name ] ...)] AS ( subquery ) [,  cte_name  [( col_name  [,  col_name ] ...)] AS ( subquery )] ... cte_name  names a single common table expression and can be used as a table reference in the statement containing the  WITH  clause. The  subquery  part of  AS ( subquery )  is called the “subquery of the CTE”  and is what produces the CTE result set. The parentheses following  AS  are required. A common table expression is recursive if its subquery refers to its own name. The  RECURSIVE  keyword must be included if any CTE in the  WITH  clause is recursive. For more information, see Recursive Common Table Expressions. Determination of column names for a given CTE occurs as follows: The number of names in the list must be the same as the number of columns in the result set. •If a parenthesized list of names follows the CTE name, those names are the column names: WITH cte (col1, col2) AS ( SELECT 1, 2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte; •Otherwise, the column names come from the select list of the first  SELECT  within the  AS ( subquery ) part: WITH cte AS ( SELECT 1 AS col1, 2 AS col2 UNION ALL SELECT 3, 4 ) SELECT col1, col2 FROM cte; A  WITH  clause is permitted in these contexts: •At the beginning of  SELECT ,  UPDATE , and  DELETE  statements. 2709