Why aren't there duplicates in a recursive SQL "range" query?

Question

For reference, here it is:

WITH range_10 AS (
  SELECT 1 AS n
           UNION ALL
  SELECT n+1 AS n
    FROM range_10
   WHERE n < 10
)
SELECT * FROM range_10;

As we all know and love, this generates a single column n with values from one through ten. However, I am confused about why there aren't duplicate values of n in the output. Here's my understanding of how this query is executed:

Iteration 1

range_10 begins empty

range_10 ends as:

1

Iteration 2

range_10 ends as:

1
2

Iteration 3

range_10 ends as:

1
2
2
3

Why? Because we SELECT'ed n+1 for all rows of the table (there were two of them), then did a UNION ALL, which doesn't remove duplicates. To make this more clear:

Iteration 4

1
2
2
3
2
3
3
4

Does UNION ALL really mean UNION in the context of recursive queries? Or am I missing something more fundamental about recursive queries?

stackoverflow.com/questions/30015842/…

Nick
– Nick

2020-03-13 06:04:06 +00:00
Commented Mar 13, 2020 at 6:04 — Nick
– Nick, Commented Mar 13, 2020 at 6:04

Martin Smith · Accepted Answer · 2020-03-13 06:03:31Z

2

As discussed here the input to each recursive execution is just the result of the previous level, not the cumulative result so far.

The anchor level emits 1. Running the recursive part on that produces 2. Running the recursive part on 2 produces 3, and so on.

answered Mar 13, 2020 at 6:03

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why aren't there duplicates in a recursive SQL "range" query?

Iteration 1

Iteration 2

Iteration 3

Iteration 4

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Iteration 1

Iteration 2

Iteration 3

Iteration 4

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related