SQL - Illustrative example of differences between UNION & UNION ALL in recursive CTE?

I'm having some trouble wrapping my head around the difference between using UNION and UNION ALL 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 fantastic lecture notes and the docs for Postgres and MySQL.

For example:

with recursive
     counter as (
        select 0 as i
        union all
        select i + 1
        from
          counter
        where
          i < 10
      )
select * from counter

returns the same results as:

with recursive
     counter as (
        select 0 as i
        union
        select i + 1
        from
          counter
        where
          i < 10
      )
select * from counter

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.

Question 1: I understand that UNION removes duplicate rows, but my question is from whence are these rows removed?

• My best guess is this is what the set difference \ operator is doing in Torsten's slides (see comparison below).

vs.

Question 2: Might someone be able to offer a good comparative case that shows when a UNION vs. UNION ALL would make a big difference in a recursive query?