INTERSECT Operator in SQL Server 2005

May 17, 2006

Microsoft introduced a new operator, INTERSECT, in SQL Server 2005.

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operator.

Let us create two databases and tables as shown below.

USE [master]
GO
/****** Object:  Database [MyDBTest]    Script Date: 04/25/2006 20:51:51 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDBTest')
DROP DATABASE [MyDBTest]
go
create database MyDBTest
go
use MyDBTest
go
create table Employee (id int, Fname varchar(50), Lname varchar(50))
go
Insert into Employee values ( 1, 'Jen' , 'Ambelang' )
Insert into Employee values ( 1, 'Jen' , 'Ambelang' )
Insert into Employee values ( 1, 'Jen' , 'Ambelang' )
Insert into Employee values ( 2, 'Alan' , 'Eechi' )
Insert into Employee values ( 3, 'Steve' , 'Borders' )
Insert into Employee values ( 4, 'Adam' , 'Carlos' )
Insert into Employee values ( 5, 'Walter' , 'Williams' )
Insert into Employee values ( 6, 'Madoka' , 'Kurosawa' )
Insert into Employee values ( 7, 'Jane' , 'Johnson' )
Insert into Employee values ( 7, 'Jane' , 'Johnson' )
Insert into Employee values ( 8, 'Hong' , 'Annie' )
Insert into Employee values ( 9, 'Lily' , 'Chang' )
Insert into Employee values ( 10, 'Frank' , 'Zhao' )
go
USE [master]
GO
/****** Object:  Database [MyDBTest2]    Script Date: 04/25/2006 20:51:51 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDBTest2')
DROP DATABASE [MyDBTest2]
go
create database MyDBTest2
go
use MyDBTest2
go
create table Employee (id int, Fname varchar(50), Lname varchar(50))
go
Insert into Employee values ( 1, 'Jen' , 'Ambelang' )
Insert into Employee values ( 2, 'Alan' , 'Eechi' )
Insert into Employee values ( 2, 'Alan' , 'Eechi' )
Insert into Employee values ( 13, 'Steve' , 'Borders' )
Insert into Employee values ( 4, 'Adam' , 'Carlos' )
Insert into Employee values ( 5, 'Walter' , 'Williams' )
Insert into Employee values ( 5, 'Walter' , 'Williams' )
Insert into Employee values ( 5, 'Walter' , 'Williams' )
Insert into Employee values ( 11, 'Nui' , 'Wanarasat' )
go

Let us query the first table, from the first database as shown below.

SELECT * FROM MyDBTest.dbo.Employee

The result is displayed below.

1

Jen

Ambelang

1

Jen

Ambelang

1

Jen

Ambelang

2

Alan

Eechi

3

Steve

Borders

4

Adam

Carlos

5

Walter

Williams

6

Madoka

Kurosawa

7

Jane

Johnson

7

Jane

Johnson

8

Hong

Annie

9

Lily

Chang

10

Frank

Zhao

Let us query the first table, from the first database as shown below.

SELECT * FROM MyDBTest2.dbo.Employee

The result is displayed below.

1

Jen

Ambelang

2

Alan

Eechi

2

Alan

Eechi

13

Steve

Borders

4

Adam

Carlos

5

Walter

Williams

5

Walter

Williams

5

Walter

Williams

11

Nui

Wanarasat

Now let us try the INTERSECT operator.

SELECT * FROM MyDBTest.dbo.Employee 
INTERSECT
SELECT * FROM MyDBTest2.dbo.Employee

The result is displayed below.

1

Jen

Ambelang

2

Alan

Eechi

4

Adam

Carlos

5

Walter

Williams

From the resultset we understand:

  1. INTERSECT operator returns only the matching rows from the queries on left and right side of the INTERSECT OPERATOR.
  2. It removed duplicates and shows only the unique rows.

The INTERSECT operator can be used with the where clause, as shown below.

SELECT * FROM MyDBTest.dbo.Employee  where Id between 2 and 9
INTERSECT 
SELECT * FROM MyDBTest2.dbo.Employee where id > 3

The result is displayed below.

4

Adam

Carlos

5

Walter

Williams

The INTERSECT operator can be used querying fewer columns and using column aliases as shown below.

SELECT Fname as FirstName,Lname  as LastName FROM MyDBTest.dbo.Employee INTERSECT 
SELECT Fname,Lname FROM MyDBTest2.dbo.Employee

The result is displayed below.

FirstName

LastName

Adam

Carlos

Alan

Eechi

Jen

Ambelang

Steve

Borders

Walter

Williams

Conclusion

This article has illustrated the functionality of INTERSECT operator in detail.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers