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.
