T-SQL INTERSECT - Finding common records between two datasets

SQL Bookstore
Home - Introduction
BASIC
Tables, Columns, Rows
SELECT Statement
INSERT Statement
UPDATE Statement
DELETE Statement
WHERE Clause
TRUNCATE Statement
GROUP BY
HAVING
Aggregation Functions
DISTINCT
INTERMEDIATE
T-SQL Operators
T-SQL Variables
T-SQL Data Types
CREATE TABLE
CREATE PROCEDURE
CREATE FUNCTION
CREATE INDEX
SQL JOIN
SQL OUTER JOIN
What is an index?
CAST / CONVERT
COALESCE
Aliases
CASE .. WHEN
Working with Dates
Working with Strings
ADVANCED
Cursors
WHILE Loop
TOP
PIVOT
UNPIVOT
SQL_VARIANT
TRY CATCH Blocks
WITH
XML
UNION
INTERSECT
EXCEPT
Other Functions
Partners
Document SSIS Packages
SSIS Tutorials
Information
Privacy Policy

Introduction

The INTERSECT keyword in TSQL is used to find what is common between two datasets. The two datasets must have the same number of columns in them and use the same datatypes. The datasets must also have the columns in the same comparative order. The INTERSECT T-SQL Expression places the INTERSECT keyword between two datasets. By using the INTERSECT keyword between two datasets, TSQL will return a new dataset that just contains the common area between both datasets. The INTERSECT keyword is often used in this fashion:

SELECT col1,col2,coln... FROM table1 '[DataSet #1]
INTERSECT
SELECT col1,col2,coln... FROM table2 '[DataSet #2]

The above image represents the use of INTERSECT between two datasets. The top circle (red) represents the first dataset, and the bottom circle (blue) represents the second dataset. The overlapping area (green) is the section that is common to both datasets. By using the INTERSECT keyword between two datasets (red & blue circles), TSQL will return a new dataset that just contains the common area between both datasets (Green Overlapping Area).

Microsoft SQL Server Standard 2008 R2 10CAL 228-09180