title: SQL - Illustrative example of differences between UNION & UNION ALL in recursive CTE?
body: <p>I'm having some trouble wrapping my head around the difference between using <code>UNION</code> and <code>UNION ALL</code> in a recursive query. In the examples I've come across in my day-to-do, often there is no apparent difference (e.g., building a running list of integers monotonically increasing by 1), but I've been trying to understand the difference more formally using Torsten Grust's <a href="https://db.inf.uni-tuebingen.de/staticfiles/teaching/ss17/advanced-sql/slides/advanced-sql-05.pdf" rel="nofollow noreferrer">fantastic lecture notes</a> and the docs for Postgres and MySQL.</p>
  <p>For example:</p>
  <pre class="lang-sql prettyprint-override"><code>with recursive
       counter as (
          select 0 as i
          union all
          select i + 1
          from
            counter
          where
            i &lt; 10
        )
  select * from counter
  </code></pre>
  <p>returns the same results as:</p>
  <pre class="lang-sql prettyprint-override"><code>with recursive
       counter as (
          select 0 as i
          union
          select i + 1
          from
            counter
          where
            i &lt; 10
        )
  select * from counter
  </code></pre>
  <p>I get the sensation there is a difference when doing hierarchical traversals but I can't find a good illustrative example of when the difference matters.</p>
  <p><strong>Question 1</strong>: I understand that <code>UNION</code> removes duplicate rows, but my question is <em>from whence</em> are these rows removed?</p>
  <ul>
  <li>My best guess is this is what the set difference <code>\</code> operator is doing in Torsten's slides (see comparison below).</li>
  </ul>
  <p><a href="https://i.stack.imgur.com/u79BF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/u79BF.png" alt="UNION in Recursive CTE" /></a></p>
  <p>vs.</p>
  <p><a href="https://i.stack.imgur.com/YaReo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YaReo.png" alt="UNION ALL in Recursive CTE" /></a></p>
  <p><strong>Question 2</strong>:  Might someone be able to offer a good comparative case that shows when a <code>UNION</code> vs. <code>UNION ALL</code> would make a big difference in a recursive query?</p>