If at least one 'Union' is used, duplicates will be removed from the entire final result set, no matter where the 'Union' occurs

-- return two row
select 1
UNION select 1
UNION ALL select 1

-- return one row
select 1
UNION ALL select 1
UNION select 1

-- return two row
select 1
UNION ALL (select 1
UNION select 1
)

To preserve duplicates in only a part of the final result set, parenthesis must be used to separate the 'union'-ed statement(s) from the 'union all'-ed statements.

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

---

You have left off half of the explanation in your quote. In the next sentence the use of parenthesis to override this behavior is explained:


That's exactly what this QotD was meant to test: If at least one union (not all) is used in the statement, all duplicates are removed. If you want to preserve (some) duplicates in the final resultset and at least one "union" (not all) is used in the statement, you will have to use parenthesis to achieve this. The query in the QotD deliberatly did not have such parenthesis to illustrate the behavior. If you follow the link to the article in the answer, you will find under "Example D" an example how to do this.