WITH statement (Common Table Expressions)

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. The following discussion describes how to write statements that use CTEs.

Syntax

|--WITH -------------------------------------------------------------------------> (1)

     .-,-----------------------------------------------------------------------+  
     V                                                                         |
(1)----- cte_name ---+---------------------+--AS (Subset of SELECT statement)--+-> (2)    
                      |    .-,--------.    |           
                      |    V          |    |           
                      '-(----column---+--)-'  
                                                                
(2)----- SELECT/UPDATE/INSERT/DELETE statement


Element	Description	Restrictions	Syntax
`column`	Name that you declare here for a column in CTE. Default is a column name from Projection list of SELECT		Identifier
cte_name	Name that you declare here for the table expression, and it can be used as a virtual table in the following SELECT/UPDATE/INSERT/DELETE statement	Must be unique among view, table, sequence, and synonym names in the database.	Identifier

Usage

WITH statement can be run directly like SELECT/UPDATE/INSERT/DELETE statements, and it can be used with

CREATE VIEW
CREATE TRIGGER
CREATE PROCEDURE/FUNCTION

Recursive CTE

A recursive CTE starts with either one non-recursive sub-query or several non-recursive sub-queries joined by UNION or UNION ALL and ends with exactly one recursive sub-query joined by UNION ALL. A recursive sub-query references the CTE being defined.

WITH recursive_cte AS (
            <initial subquery>
            UNION ALL
            <recursive subquery>
) SELECT …

Example 1: Recursive query computing the factorial of numbers from 0 to 9

WITH temp (n, fact) AS (
 SELECT 0, 1                                           -- Initial Subquery
  UNION ALL 
 SELECT n+1, (n+1)*fact FROM temp       -- Recursive Subquery 
        WHERE n < 9)
SELECT * FROM temp;

Other example of computing Fibonacci Numbers 
WITH fib(p, n) as (
    select 0, 1                              -- initial subquery
    UNION ALL                            -- ‘UNION ALL’
    select n, (p+n) from fib -- recursive subquery
             where n < 100                -- terminate condition
)
select p as fn from fib;
        fn
          0
          1
          1
          2
          …
         34
         55
         89

Example 2: Recursive CTE optional Cycle clause

CYCLE <column list> SET <cycle pseudo column> 
         TO <value1> DEFAULT <value2>

create table cycle (id int, pid int);
insert into cycle values (1,2);
insert into cycle values (2,1);

WITH cte AS (
    select id, pid from cycle where id = 1 UNION ALL
    select t.id, t.pid   
               from cycle t, cte where t.pid = cte.id)
cycle id set iscycle to "yes" default "no"
SELECT id, pid, iscycle from cte ;
             id         pid iscycle 
          1           2 no  
          2           1 yes

Example 3: Recursive CTE Loops

with cte(n) as 
(
    select 1
    UNION ALL
    select n+1 from cte
)
select first 2 n from cte;            -- 2 rows return

without the ‘FIRST 2’ the query will loop till integer limit error.