WITH Clause In Oracle PLSQL

A common table expression (CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.

To specify common table expressions, use a WITH clause that has one or more comma-separated subclauses. Each subclause provides a subquery that produces a result set, and associates a name with the subquery.

    WITH
        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 following example defines CTEs named cte1 and cte2 in the WITH clause, and refers to them in the top-level SELECT that follows the WITH clause:

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.

Determination of column names for a given CTE occurs as follows:

• 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;

  The number of names in the list must be the same as the number of columns in the result set.

• 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 CTE can refer to itself or to other CTEs:

• A self-referencing CTE is recursive.

• A CTE can refer to CTEs defined earlier in the same WITH clause, but not those defined later.

  This constraint rules out mutually-recursive CTEs, where cte1 references cte2 and cte2 references cte1. One of those references must be to a CTE defined later, which is not permitted.

• A CTE in a given query block can refer to CTEs defined in query blocks at a more outer level, but not CTEs defined in query blocks at a more inner level.

A recursive common table expression is one having a subquery that refers to its own name. For example:

WITH cte (n) AS
(
  SELECT 1 from dual
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

When executed, the statement produces this result, a single column containing a simple linear sequence:

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

The recursive CTE subquery has two parts, separated by UNION [ALL] or UNION DISTINCT:

SELECT ...      -- return initial row set
UNION ALL
SELECT ...      -- return additional row sets

The first SELECT produces the initial row or rows for the CTE and does not refer to the CTE name. The second SELECT produces additional rows and recurses by referring to the CTE name in its FROM clause. Recursion ends when this part produces no new rows. Thus, a recursive CTE consists of a nonrecursive SELECT part followed by a recursive SELECT part.

Each SELECT part can itself be a union of multiple SELECT statements.

WITH cte(n, str) AS
(
  SELECT 1, 'abc' from dual
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

When executed, the statement produces this result:

+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+