What are Sql Recursive Queries known also as Hierarchical Queries?

Data System Architecture

About

A recursive query is a query that is recursive.

With Recursive statement

The general standard format is with recursive

WITH RECURSIVE generate_series(value) AS (
  SELECT $start
  UNION ALL
  SELECT value+$step FROM generate_series
   WHERE value+$step<=$end
) 
SELECT * FROM generate_series

This is the equivalent of the generate_series function (Sqlite, PostgreSQL, …)

  • For a sequence every 1
WITH RECURSIVE aux(i) AS (
  VALUES (1)
  UNION ALL
  SELECT i+1 FROM aux WHERE i < 10
)
SELECT * FROM aux
1, 2, 3, 4, 5, 6, 7, 8, 9

  • For a sequence every 30
with recursive cte(sequence) as (
    select 30
    union all
    select sequence + 30
    from cte
    where sequence < 180
)
select *
from cte

Example by database

MySql

with recursive sequence as (
select 1 as id 
union all
select id + 1 as id from sequence
where id < 10)
select * from sequence

Oracle

Oracle recursive statement is known as the connect by

Select Rownum
From dual
Connect By Rownum <= 100





Discover More
Card Puncher Data Processing
Calcite - Recursive Query

recursive query in Calcite. A SQL recursive query, e.g. this one that generates the sequence 1, 2, 3, …10: Using a scan on a TransientTable and a RepeatUnion:
Data System Architecture
How to generate data Just With Sql ?

data generation with just sql is based on then generation of integer sequence via recursive query Example: for mysql:



Share this page:
Follow us:
Task Runner