title: Why aren't there duplicates in a recursive SQL "range" query?
body: <p>For reference, here it is:</p>
  
  <pre><code>WITH range_10 AS (
    SELECT 1 AS n
             UNION ALL
    SELECT n+1 AS n
      FROM range_10
     WHERE n &lt; 10
  )
  SELECT * FROM range_10;
  </code></pre>
  
  <p>As we all know and love, this generates a single column <code>n</code> with values from one through ten. However, I am confused about why there aren't duplicate values of <code>n</code> in the output. Here's my understanding of how this query is executed:</p>
  
  <h3>Iteration 1</h3>
  
  <p>range_10 begins empty</p>
  
  <p>range_10 ends as:</p>
  
  <ul>
  <li>1</li>
  </ul>
  
  <h3>Iteration 2</h3>
  
  <p>range_10 ends as:</p>
  
  <ul>
  <li>1</li>
  <li>2</li>
  </ul>
  
  <h3>Iteration 3</h3>
  
  <p>range_10 ends as:</p>
  
  <ul>
  <li>1</li>
  <li>2</li>
  <li>2</li>
  <li>3</li>
  </ul>
  
  <p>Why? Because we <code>SELECT</code>'ed n+1 for all rows of the table (there were two of them), then did a <code>UNION ALL</code>, which doesn't remove duplicates. To make this more clear:</p>
  
  <h3>Iteration 4</h3>
  
  <ul>
  <li>1</li>
  <li>2</li>
  <li>2</li>
  <li>3</li>
  <li>2</li>
  <li>3</li>
  <li>3</li>
  <li>4</li>
  </ul>
  
  <p>Does <code>UNION ALL</code> really mean <code>UNION</code> in the context of recursive queries? Or am I missing something more fundamental about recursive queries?</p>