# 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.``````