INTERSECT Operator in SQL Server 2005

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles