Click here to Skip to main content
15,850,359 members
Articles / Database Development / SQL Server / SQL Server 2012

Get Ready to Learn SQL Server: 17. How to Use the Intersect Operator

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
31 Oct 2014MIT3 min read 10.2K   8   2
How to use the Intersect Operator in SQL Server

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

How To Use the Intersect Operator

The INTERSECT operator is used to combine like rows from two queries. It returns rows that are common between both results. To use the INTERSECT operator, both queries must return the same number of columns and those columns must be of compatible data types.

Visual Example of Intersect

In this example, the circles represent two queries. The orange circle is the left query; whereas, the blue circle is the right. The area within each circle represents that query’s results.

Image 1

Visual Explanation of the Intersect Operator

As you can see, the green portion represents the result of the INTERSECT operator. This area represents those rows that are in both the left and right query.

Example

Below is the general format of the INTERSECT operator.

SQL
SELECT Name, BirthDate FROM Employee
INTERSECT
SELECT Name, BirthDate FROM Customer

There are two queries which are separated by the INTERSECT operator. The top query is commonly called the left query.

The query is valid since both the left and right queries contain the same number of columns and each column is a similar data type; Char and Date respectively.

Contrast this to:

SQL
SELECT Name, BirthDate FROM Employee
INTERSECT
SELECT Age, BirthDate, Name FROM Customer

Which is invalid on multiple levels. First, the number of columns isn’t the same. Additionally, the data type for each column is incompatible. For instance, Name, which is a Char column isn’t a compatible data type with Age.

Uses for Intersect

The intersect operator is good when you want to find common rows between two results. The INTERSECT operator is similar to the AND operator; however, they operate on different database objects.

The Intersect operator is used to compare entire rows; whereas, the AND operator is used to compare columns within rows.

Say what?

Don’t worry, it becomes clearer below.

Intersect Two Tables

Let’s assume we want to find all job titles for positions held by both male and female employees. How could we do this? The first set is to compose the queries to find positions held by males, then to do the same for females.

Here is the query for males, the one for the females is very similar:

SQL
SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'

To finish, we need to find out which titles are in common. To do this, we can use the INTERSECT operator.

SQL
SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'
INTERSECT
SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'F'

You may be tempted to try and simplify this statement by eliminating the INTERSECT operator all together and use the following:

SQL
SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'
       AND Gender = 'F'

But this won’t simply work. Why? Because the Where clause is evaluated for each row and you’re never going to find a Gender value equal to both M and F for the same record.

Order By

To order the result by JobTitle, we can use an ORDER BY clause. Keep in mind that this works on the final row set returned by the intersect operator.

SQL
SELECT   JobTitle
FROM     HumanResources.Employee
WHERE    Gender = 'M'
INTERSECT
SELECT   JobTitle
FROM     HumanResources.Employee
WHERE    Gender = 'F'
ORDER BY JobTitle

Equivalence

The INTERSECT hasn’t always been part of SQL Server. Before its introduction to the language, you had to mimic the INTERSECT behavior using an INNER JOIN.

Below is the equivalent statement to find job titles in common for both genders:

SQL
SELECT DISTINCT M.JobTitle
FROM   HumanResources.Employee AS M
       INNER JOIN
       HumanResources.Employee AS F
       ON M.JobTitle = F.JobTitle
          AND M.Gender = 'M'
          AND F.Gender = 'F'

This join is called a self-join, since we are joining the table to itself. The idea is to match up every JobTitle with same values. By pairing these values together, we can then compare their corresponding gender values and keep those where one gender is male and the other female.

NOTE: These are equivalent to a point. AS we have learned, NULL aren’t values, therefore NULL = NULL is always false. Given this, the INNER JOIN will fail to match on joins; howver, the INTERSECT operator does match NULLs.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
BugA small error Pin
SpArtA1-Nov-14 1:42
SpArtA1-Nov-14 1:42 
GeneralRe: A small error Pin
essentialSQL1-Nov-14 2:06
essentialSQL1-Nov-14 2:06 
Hi! Thanks for pointing that out. I have just fixed it in the original blog and have submitted the change on the CodeProject version.
Kris - www.essentialsql.com

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.