R.P.Rozema (3/8/2012)

---

To finalize this: My explanation is wrong and my question + answer was correct only by luck.

The proper explanation has been given in this thread. To be sure I am getting it now I'll try to summarize it:

Union queries are interpreted left to right. If "union all" is followed by "union", the "union all" will return duplicates, but these will be filtered by the following "union". Other way around, if "union" is followed by "union all", any duplicates from the first 2 statements are filtered, but new duplicates may be introduced by the following "union all".

Parenthesis can be used to override the left-to-right evaluation.

An illustration can be given by putting more rows in the test tables:

create table #t1 (col int not null);

create table #t2 (col int not null);

create table #t3 (col int not null);

insert #t1 (col) values(1), (1);

insert #t2 (col) values(2), (2);

insert #t3 (col) values(3), (3);

select col from #t1

UNION

select col from #t2

UNION ALL

select col from #t3;

select col from #t1

UNION ALL

select col from #t2

UNION

select col from #t3;

And now the results are:

col

-----------

1

2

3

3

(4 row(s) affected)

col

-----------

1

2

3

(3 row(s) affected)

Seems like I was the first to learn something from my own question :).

Thanks for all the feedback!