Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 17, 2006

INTERSECT Operator in SQL Server 2005

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM