Home > SQL Server > SQL SERVER – INTERSECT vs EXCEPT Set Operators with Examples and Query Plan

SQL SERVER – INTERSECT vs EXCEPT Set Operators with Examples and Query Plan


Intersect and Except are the set operators that are used to find the distinct result from multiple SQL query results.

Union is another set operator that I have discussed in a separate blog post refer UNION VS UNION ALL.

In this blog post, I am going to discuss about INTERSECT and EXCEPT set operator and I have also included the execution plan to make the reading more interesting.

Intersect

INTERSECT is used to fetch any distinct values that are in both the query on the left and right sides of the INTERSECT operand.

Union 

UNION Combine all result sets and produce unique records (exclude duplicate records)

So, Intersect and Union set operators will produce the same result but there is a difference in the query plan. I will show you the difference in the example section of this blog.

Except

EXCEPT fetches all distinct values from the left query and that values should not be found in the right query result.

Implementation

I am using AdventureWork2012 database in SQL SERVER 2014 CTP1 instance.

1. Intersect, get all distinct values from the left and right query

Query

select [FirstName],[MiddleName],[LastName] from [Person].[Person]
INTERSECT
select [FirstName],[MiddleName],[LastName] from [Person].[Person]

Output

pic1If you look at the above query execution plan, Intersect set operator uses the merge join with Left Semi Join logical operator and when we you use union set operator then the optimizer uses Merge join with Union logical operator. But, the output of both Intersect and Union are same.

2. Union, Executing the following SQL query with Union set operator

select [LastName] from [Person].[Person]
union
select [LastName] from [Person].[Person]

Output

pic2Query optimizer choose the best plan based on the different criteria. So, learn about query plan operators to interpret the execution plan accurately.

Hint: Merge join is particularly effective if explicit sorting is not required, for example, if there is a suitable B-tree index is available.

3. Except, executing the below given SQL query with except set operator

select LastName from [Person].[Person]

except

select LastName from [Person].[Person]

Output

This query return zero rows because both query results are same. So, Look at the execution plan. It uses Merge join with Left Anti Semi Join.

pic4In the below example, I have used a constant value in the left side of the except operator and this value is unique and not found in right side result. So, it returns the left side value only in the output result set.

pic5I hope you have understood the difference between Intersect, Except and Union set operators in SQL Server.

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: