SQL Server 2005 - INTERSECT and EXCEPT operands.

INTERSECT and EXCEPT operands:


Like Union and Union all SQL Server 2005 introduces INTERSECT and EXCEPT operands.

INTERSECT :

It is useful for retrieving only distinct rows that exist in both the left and right queries.

EXCEPT :

It is useful for retrieving only distinct rows that exist in the left query.

For example :

Create table DeptA

( DeptId int, DeptName varchar(40))

GO

Create table DeptB

( DeptId int, DeptName varchar(40))

GO

insert into DeptA values(10,'Sales')

GO

insert into DeptA values(20,'Service')

GO

insert into DeptA values(30,'Puchase')

GO

insert into DeptB values(10,'Sales')

GO

insert into DeptB values(20,'Service')

GO

insert into DeptB values(30,'Marketing')

GO

--Intersect

select * From DeptA

Intersect

Select * From DeptB

GO

--Except

select * From DeptA

Except

Select * From DeptB

GO