Set operators allow you to combine data from two or more queries and return the results in a single set.  There are 3 set operators available T-SQL

EXCEPT, INTERSECT and UNION.

This article is focused on the UNION operator.  UNION is used when you need to combine data from multiple queries into a single result set that contains all data from the queries.  There are a few rules to follow when building queries using a UNION operator.  First, all data types must be compatible from all of the queries.  Second the number and order of the columns being combined must be the same in all queries.  Union also includes a very important argument – ALL.  When you include ALL in your UNION statement duplicates are included in the result set.  Without using the ALL argument, duplicates are removed.  If you would like to order the final result set the last select statement in the query should include the ORDER BY clause.

The following is a simple example of using UNION in the AdventureWorks 2014 Data Warehouse database:

SELECT [AccountKey]

      ,[ParentAccountKey]

      ,[AccountCodeAlternateKey]

      ,[ParentAccountCodeAlternateKey]

      ,[AccountDescription]

      ,[AccountType]

  FROM [AdventureWorksDW2014] .[dbo]. [DimAccount]

  where AccountType = ‘assets’

  UNION

SELECT [AccountKey]

      ,[ParentAccountKey]

      ,[AccountCodeAlternateKey]

      ,[ParentAccountCodeAlternateKey]

      ,[AccountDescription]

      ,[AccountType]

  FROM [AdventureWorksDW2014] .[dbo]. [DimAccount]

  where AccountType = ‘revenue’

  order by AccountCodeAlternateKey

This will return all the columns in a single result set.  Notice how both queries contain the same number of columns and each column is in the same order.  To order the result set the last select statement contains the order by clause.

When working on projects the UNION clause is great for when you need to build data from multiple sources.  For instance, if you have 2 product tables and you want to build a new master product table.  First define the master product table and its columns.  Then you can write a query that selects the data from the first source system and then second source system using the UNION operator.  The result from this query will be the unique records from both systems.

For complete information on UNION see MSDN – https://msdn.microsoft.com/en-us/library/ms180026.aspx