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:
- INTERSECT
operator returns only the matching rows from the queries on left and right
side of the INTERSECT OPERATOR. - 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.