Except operator in MSSQL2005

remeber i go to the company that freeborde the hr ask that what is except? how do you it?
on that time i can't answer it.

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.