Except Operator in SQL Server 2005

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

Latest Articles