WITH

The WITH clause defines one or more common table expressions (CTEs) that can be used in the subsequent statement. These CTEs, for example, can be referenced in the subsequent FROM clause.

A CTE can be thought of as a "temporary view" or named subquery that can be used later in the query where the CTE was defined. The CTE defines:

• The name of the "temporary view"
• A list of column names and a query expression (a SELECT statement) (optional).

The result of the CTE is essentially a table, where each column corresponds to a column in the (optional) list of column names.

The WITH clause is optional.

See also CONNECT BY and Common Table Expressions guide.

Syntax

[WITH
      cte_name1 [( <cte_col_list> )] AS (SELECT ...)
   [, cte_name2 [( <cte_col_list> )] AS (SELECT ...)]
   [, cte_nameN [( <cte_col_list> )] AS (SELECT ...)]
]
SELECT ...

Parameters

cte_name1 ... cte_nameN: The CTE name must follow the rules for object identifiers.

---

cte_col_list: The names of the columns in the CTE.

Usage Notes

• A WITH clause can reference other preceding CTEs listed in the same clause. Example: cte_name2 can reference cte_name1, while cte_name1 cannot reference cte_name2.

• cte_col_list is optional.

Example 1 | Simple CTE

WITH
  cte_2cols AS ( SELECT * FROM  (SELECT 'a' AS ord1, '10' AS col1, 50 AS col2
                                UNION ALL 
                                SELECT 'b' AS ord1,  '20' AS col1, 40 AS col2
                                UNION ALL
                                SELECT 'c' AS ord1,  '30' AS col1, 30 AS col2
                                UNION ALL 
                                SELECT 'a' AS ord1,  '40' AS col1, 20 AS col2 
                                UNION ALL 
                                SELECT 'b' AS ord1,  '50' AS col1, NULL AS col2))
SELECT ord1, col2 FROM cte_2cols ORDER BY col2 DESC;

+------+------+ 
| ORD1 | COL2 | 
+------+------+
| b    | NULL | 
| a    | 50   | 
| b    | 40   | 
| c    | 30   | 
| a    | 20   | 
+------+------+

Example 2 | Multiple CTEs in a Query

WITH
  cte_2cols AS ( SELECT * FROM  (SELECT 'a' AS ord1, '10' AS col1, 50 AS col2
                                UNION ALL 
                                SELECT 'b' AS ord1,  '20' AS col1, 40 AS col2
                                UNION ALL
                                SELECT 'c' AS ord1,  '30' AS col1, 30 AS col2
                                UNION ALL 
                                SELECT 'a' AS ord1,  '40' AS col1, 20 AS col2 
                                UNION ALL 
                                SELECT 'b' AS ord1,  '50' AS col1, NULL AS col2)),

  cte_2cols_where AS (SELECT ord1, col1, col2 FROM cte_2cols 
                            WHERE col2 IS NULL)
  SELECT * FROM cte_2cols_where;

+------+------+------+ 
| ORD1 | COL1 | COL2 | 
+------+------+------+
| b    | 50   | NULL | 
+------+------+------+

Example 3 | "Implicit View"

WITH
  cte_2cols1 AS ( SELECT * FROM  (SELECT 'a' AS ord, '10' AS col1
                                  UNION ALL 
                                  SELECT 'b' AS ord,  '20' AS col1
                                  UNION ALL
                                  SELECT 'c' AS ord,  '30' AS col1
                                  UNION ALL
                                  SELECT 'd' AS ord,  '40' AS col1)),
  cte_2cols2 AS ( SELECT * FROM  (SELECT 'c' AS ord, 50 AS col2
                                  UNION ALL 
                                  SELECT 'd' AS ord,  40 AS col2
                                  UNION ALL
                                  SELECT 'e' AS ord,  30 AS col2)),
  cte_2cols_1u2 AS (SELECT cte_2cols1.ord, cte_2cols1.col1, cte_2cols2.col2 
                           FROM cte_2cols1 INNER JOIN cte_2cols2 
                                  ON cte_2cols1.ord = cte_2cols2.ord)
SELECT * FROM cte_2cols_1u2 WHERE ord = 'c';

+------+------+------+ 
| ORD1 | COL1 | COL2 | 
+------+------+------+
| c    | 30   | 50   | 
+------+------+------+