Hierarchical and recursive queries in SQL

Recursive queries are usually used in hierarchical queries. 

1.Semantics of recursive query

with recursive T(a1,a2,...) as

(

non-recursive select

        UNION [ALL]

recursive select involving T

)

select..... from T where ....

non-recursive select:

1.does not refer to T

2.generates an initial set of  tuples for T

recursive select:

1.must be a query involving T

2. must include a where condition

3.must eventually return in empty result

2.Example: generate sum of first 100 integers

with recursive nums(n) as (

    select 1

  union
    select n+1 from nums where n < 100
)
select sum(n) from nums;

 sum  
------
 5050