Nice question.

The execution plan for slightly modified query

SELECT 2 UNION

SELECT 1 union

SELECT NULL UNION

SELECT '1' union

SELECT '2'

shows that Merge Join operators are used to merge inputs from particular SELECT queries. BOL says that Merge Join operator may introduce sort as it requires inputs to be sorted. Since the Selects return only single values, they are 'sorted' and we don't see explicit sort in execution plan.

Interesting thing happens when I modify the query to following code:

SELECT 2 UNION

select a

from

(

SELECT '2' a UNION ALL

SELECT 1

) q UNION

SELECT NULL UNION

SELECT '1'

The Merge Joins are replaced by Concatenation operator and explicit Distinct Sort is used to remove duplicates. If I add option (merge union) to above query, the sort operation is added on the input of the Merge Join implemening union SELECT 2 UNION

select a

from ....

I wonder if it is possible to get output 1, NULL, 2 or 2, NULL, 1. The Hash Match operator that is used when option (hash union) is used apparently sorts data anyway, just NULLs are added to the end of the output.

Piotr