In this step by step SQL Tutorial I will show you how to use INTERSECT operator. For the purpose of this tutorial I will use SQL Server 2012.
Who is it for?
- People who would like to learn basics of using INTERSECT
- Those who are preparing for Microsoft certifications and need to cover this topic.
Prior knowledge. If you are new to SQL or new to certain SQL Server features you might find links below useful when working with this tutorial:
- SQL Union - check the most popular operators that combines two queries
- SQL Except - check how to shows distinct rows from one query without rows from the second query
We are going to show you how to compare result of two queries using INTERSECT statement and return only distinct (unique) rows that exist in both queries.
To better understand our examples let's create a task that is close to real-life.
Imagine it's Christmas time and you have been asked to identify customers that might buy something this Christmas. These customers will be send "a gift" that will encourage them to buy something new. Budget is limited and you need to focus on the most promising customers.
You decided that the best customers are the ones that bought something in December last year and the year before. You just need to write a SQL query to identify those customers.
In the following example I will show you how you could solve your task.
SQL INTERSECT statement using SSMS 2012
Before we start let's briefly discuss INTERSECT behaviour and rules:
- INTERSECT returns DISTINCT (unique) rows that exists in both queries
- You need the same number of fields; in the same order
- Data type for each matching field should be the same
In our example we have to queries:
One returns Customers (CustomerKey field - we won't bother with joins to customer table) that bought something in December 2007.
Second T-SQL query returns Customers (CustomerKey field) that bought something in December 2006
Our task is to return Customers that bought something both in December 2007 and December 2006. In order to do that we will use INTERSECT.
See below example that returns 44 unique customers (CustomerKey) that are best candidates to receive a gift this Christmas!
I hope the exam above will be usefull with your SQL INTERSECT queries