Sql server® 2012 t-sql fundamentals

The 

UNION

 distinct Set Operator

The UNION (implicit DISTINCT) set operator unifies the results of the two queries and eliminates 

duplicates. Note that if a row appears in both input sets, it will appear only once in the result; in other 

words, the result is a set and not a multiset. 

For example, the following code returns distinct locations that are either employee locations or 

customer locations.

SELECT country, region, city FROM HR.Employees 

UNION 

SELECT country, region, city FROM Sales.Customers;

www.it-ebooks.info

---

194  

Microsoft SQL Server 2012 T-SQL Fundamentals

The difference between this example and the previous one with the UNION ALL operator is that in 

this example, the operator removed duplicates, whereas in the previous example, it didn’t. Hence, the 

result of this query has 71 distinct rows, as shown here in abbreviated form.

country         region          city 

--------------- --------------- --------------- 

Argentina       NULL            Buenos Aires 

Austria         NULL            Graz 

Austria         NULL            Salzburg 

Belgium         NULL            Bruxelles 

Belgium         NULL            Charleroi 

... 

USA             WY              Lander 

Venezuela       DF              Caracas 

Venezuela       Lara            Barquisimeto 

Venezuela       Nueva Esparta   I. de Margarita 

Venezuela       Táchira         San Cristóbal 

 

(71 row(s) affected)

So when should you use UNION ALL and when should you use UNION? If a potential exists for du-

plicates after the two inputs of the operator have been unified, and you need to return the duplicates, 

use UNION ALL. If a potential exists for duplicates but you need to return distinct rows, use UNION. If 

no potential exists for duplicates after the two inputs have been unified, UNION and UNION ALL are 

logically equivalent. However, in such a case I’d recommend that you use UNION ALL because adding 

ALL removes the overhead incurred by SQL Server checking for duplicates.