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.

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

SELECT * FROM MyDBTest2.dbo.Employee

The result is displayed below.

Now let us try the INTERSECT operator.

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

The result is displayed below.

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.

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.

Conclusion

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

» See All Articles by Columnist MAK