Recursive WITH and CONNECT BY

A recursive WITH refers to itself: a starting query (the anchor) and a query that builds the next rows from the previous ones, repeated until it returns nothing. Oracle's older CONNECT BY does the same job with its own syntax. Two classic uses: generating rows (every day of a month) and walking a chain (row to next row, or parent to child).

Part 1: generating the days you don't have

The test case

The first week of the 2025 season, April 5 to 11: every day, its weekday, and how many matches were played that day (including 0).

The matches table only has rows for match days; the empty days have to be made up.

A match every other day: the players get a day off to rest, travel and argue about selection.

Way 1: recursive WITH

with days (d) as (
  select date '2025-04-05' from dual                  -- anchor: the first day
  union all
  select d + 1 from days where d < date '2025-04-11'  -- recursive step: the next day, until the 11th
)
select to_char(days.d, 'YYYY-MM-DD')                             as match_day,
       to_char(days.d, 'Dy', 'NLS_DATE_LANGUAGE = ENGLISH')      as dow,
       count(m.match_id)                                         as matches
from   days
left   join cr_matches m on m.match_date = days.d
group  by days.d
order  by days.d;

• The column list days (d) is required for a recursive WITH.
• The recursive part reads days itself: each round adds d + 1 until the WHERE stops it. Forget that condition and it keeps going until it runs out of resources.
• 'NLS_DATE_LANGUAGE = ENGLISH' makes Dy give English day names whatever the session language.

Way 2: CONNECT BY LEVEL

with days as (
  select date '2025-04-05' + level - 1 as d
  from   dual
  connect by level <= 7
)
select to_char(days.d, 'YYYY-MM-DD')                             as match_day,
       to_char(days.d, 'Dy', 'NLS_DATE_LANGUAGE = ENGLISH')      as dow,
       count(m.match_id)                                         as matches
from   days
left   join cr_matches m on m.match_date = days.d
group  by days.d
order  by days.d;

SELECT ... FROM dual CONNECT BY LEVEL <= 7 is the famous Oracle row generator: 7 rows, with LEVEL numbering them 1 to 7. Shorter than recursive WITH, and Oracle-only.

Part 2: walking a sequence, one step at a time

The test case

The first six overs of match 1, first innings: the runs in each over and the score at the end of it.

Way 1: recursive WITH carrying a running total

with overs as (
  select over_no, sum(total_runs) as runs
  from   cr_balls_v
  where  match_id = 1 and innings_no = 1
  group  by over_no
),
walk (over_no, runs, score) as (
  select over_no, runs, runs from overs where over_no = 1                 -- start at over 1
  union all
  select o.over_no, o.runs, w.score + o.runs                              -- add the next over
  from   walk w
  join   overs o on o.over_no = w.over_no + 1
  where  o.over_no <= 6
)
select over_no, runs, score from walk order by over_no;

Each round joins the previous row to the next over and carries the score forward. This is how you'd compute something where each step depends on the previous result (a balance with interest, a stock level after each order), which window functions can't always express.

Way 2: a running SUM

select over_no, runs, sum(runs) over (order by over_no) as score
from   (select over_no, sum(total_runs) as runs
        from   cr_balls_v
        where  match_id = 1 and innings_no = 1
        group  by over_no)
where  over_no <= 6
order  by over_no;

For a plain running total, the window function is simpler and much faster. Here the WHERE can sit in the same query because overs 1 to 6 don't depend on anything later.

19c and 26ai

The same on both.

Gotchas

• Always give recursion a way to stop. A WHERE in the recursive part, or a join that eventually finds nothing.
• Recursive WITH needs UNION ALL, not UNION.
• CONNECT BY for trees (START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id) has extras like SYS_CONNECT_BY_PATH and CONNECT_BY_ISLEAF; recursive WITH has SEARCH DEPTH FIRST and CYCLE clauses. Our cricket data is flat, so trees get their own dataset in Part 13.