dcsimg

Except Operator in SQL Server 2005

May 3, 2006

Microsoft introduced the EXCEPT operator in SQL Server 2005, which returns all of the distinct rows from the left side of the EXCEPT operator. It also removes all of the rows from the result set that match the rows that are on the right side of the EXCEPT 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 ( 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 ( 13, 'Steve' , 'Borders' )
Insert into Employee values ( 4, 'Adam' , 'Carlos' )
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 shown below.

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 second database as shown below.

SELECT * FROM MyDBTest2.dbo.Employee

The result is shown below.

1

Jen

Ambelang

2

Alan

Eechi

13

Steve

Borders

4

Adam

Carlos

5

Walter

Williams

11

Nui

Wanarasat

Now let us try the EXCEPT operator.

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

The result is shown below.

3

Steve

Borders

6

Madoka

Kurosawa

7

Jane

Johnson

8

Hong

Annie

9

Lily

Chang

10

Frank

Zhao

From the result, we understand the following.

a.      The row with Id = 7 had duplicates in MyDBTest.dbo.Employee but in the resultset only distinct value came out
b.      The rows with id =1, id =2, Id=4, Id=5 are removed from the resultset because it exists in MyDBTest2.dbo.Employee

The EXCEPT operator could be used with where clause as shown below.

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

The result is shown below.

2

Alan

Eechi

3

Steve

Borders

6

Madoka

Kurosawa

7

Jane

Johnson

8

Hong

Annie

9

Lily

Chang

The EXCEPT operator could be used with fewer columns and column aliases as shown below.

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

The result is shown below.

FirstName

LastName

Frank

Zhao

Hong

Annie

Jane

Johnson

Lily

Chang

Madoka

Kurosawa

You can combine the select statement with group by operator as shown below

SELECT Count(id),max(FName) ,max(LName) FROM 
MyDBTest.dbo.Employee group by [id]
EXCEPT 
SELECT id,Fname,Lname FROM MyDBTest2.dbo.Employee

The result is shown below.

1

Adam

Carlos

1

Alan

Eechi

1

Frank

Zhao

1

Hong

Annie

1

Lily

Chang

1

Madoka

Kurosawa

1

Steve

Borders

1

Walter

Williams

2

Jane

Johnson

Conclusion

This article has illustrated the functionality of the EXCEPT 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