Sql server® 2012 t-sql fundamentals

The 

UNION ALL

 Multiset Operator

The UNION ALL multiset operator returns all rows that appear in any of the input multisets resulting 

from the two input queries, without really comparing rows and without eliminating duplicates. As-

suming that Query1 returns m rows and Query2 returns n rows, Query1 UNION ALL Query2 returns 

m + n rows. 

www.it-ebooks.info

---

 

CHAPTER 6

 

Set Operators

 

 193

For example, the following code uses the UNION ALL operator to unify employee locations and 

customer locations.

USE TSQL2012; 

 

SELECT country, region, city FROM HR.Employees 

UNION ALL 

SELECT country, region, city FROM Sales.Customers;

The result has 100 rows—9 from the Employees table and 91 from the Customers table—and is 

shown here in abbreviated form:

country         region          city 

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

USA             WA              Seattle 

USA             WA              Tacoma 

USA             WA              Kirkland 

USA             WA              Redmond 

UK              NULL            London 

UK              NULL            London 

UK              NULL            London 

... 

Finland         NULL            Oulu 

Brazil          SP              Resende 

USA             WA              Seattle 

Finland         NULL            Helsinki 

Poland          NULL            Warszawa 

 

(100 row(s) affected)

Because UNION ALL doesn’t eliminate duplicates, the result is a multiset and not a set. The same 

row can appear multiple times in the result, as is the case with (UK, NULL, London) in the result of this 

query.