• It's because of the order of operations: when written this way the UNION happens first and removes duplicates in t2 and t3, then the result is combined with t1 using UNION ALL which allows duplicates.

    select col from #t2

    union

    select col from #t3

    union all

    select col from #t1

    If you change round the UNION and UNION ALL above you get the result set without duplicates, because the last operation performed is a UNION.

    select col from #t2

    union all

    select col from #t3

    union

    select col from #t1