Home > T-SQL > SQL Server SET Operators – UNION, UNION ALL, INTERSECT, EXCEPT – @SQL Server

SQL Server SET Operators – UNION, UNION ALL, INTERSECT, EXCEPT – @SQL Server


Set operators are used to combine two or more result set into one data set or in some cases, set operators are used to pick-up unique data from multiple data-sets . It is always necessary while working in data warehouse scenario that means in large volume of data . In SSIS, we have UNION ALL Transformation in the data flow environment to combine multiple data-sets.

But in transact SQL, we have multiple set operators to perform data merging operations.

SET operators in SQL Server (T-SQL) are:

  1. UNION
  2. UNION ALL
  3. EXCEPT
  4. INTERSECT

UNION OR UNION ALL

Union or Union All set operators are used to combine two or more similar dataset. It means,  all columns in each dataset must be equal and in same order and It should have sane data type.

UNION ALL – This set operator merges multiple datasets and it includes duplicate rows if any.

UNION – This set operator merges multiple datasets and it eliminates duplicate rows if any. It give unique record from all data set.

Example

USE AdventureWorks
--- union
select Name, CostRate from Production.Location
UNION
select Name, CostRate from Production.Location
UNION
select Name, CostRate from Production.Location

--- union all
select Name, CostRate from Production.Location
UNION ALL
select Name, CostRate from Production.Location
UNION ALL
select Name, CostRate from Production.Location

EXCEPT & INTERSECT 

Both operators returns distinct values by comparing two queries results.

But,

EXCEPT returns any distinct values from the left query and that value should not be in right query.

INTERSECT returns any distinct values that are returned by both the query

Example

Intersect set operator

with cte1 (name, cost)
 as (
select 'aa', 1
union all
select 'bb', 1
union all
select 'cc', 1
)

SELECT * FROM cte1

INTERSECT

SELECT * FROM cte1

except set operator

use AdventureWorks

select Title from HumanResources.Employee where EmployeeID % 2 =0

except

select Title from HumanResources.Employee where EmployeeID % 2 ! =0
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: