Union in SQL Server Example

Let's learn how to use of SQL UNION Operator in SQL Query analyzer.

When to use union operator in SQL?
we use sql union operator to combine the result-set of multiple select statements, there are different type of union statement, union all statement combines multiple result set with duplicate values.

How to write SQL Union Syntax

Let’s understand how to use union in sql statement, in below example we have combined two sql statements using union key word, as a result you can see one result set brining all records together.

Select Firstname, Email from [dbo].[tbClient]
union 
Select Firstname, Email from [dbo].[tbStudent]

Apart from combining multiple select statements into one result set, union also distinct data, which means it combines all records then remove all duplicate data.

For example, if query one has result of 1,2,3 and query two returns 3,4,5 then the union clause will combine all the records and remove the duplicate 3 from result set and return the final result of 1,2,3,4,5

Just like above example, now in place of union we write union all and see the difference in result set, sql union all multiple tables.

Select Firstname, Email from [dbo].[tbClient]
union all
Select Firstname, Email from [dbo].[tbStudent]

Unlike union operator, union all will combine two or more multiple select query and produce one result set, and keep all the duplicate records.