SQL Server provides the following set operators. Set operators combine results from two or more queries into a single result set

1) Intersect and Except

2) Union

Today in this post we will learn Intersect and Except set operators

Returns distinct values by comparing the results of two queries.

EXCEPT returns any distinct values from the left query that are not also found on the right query.

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

 

Lets take a simple example and you will better understand the set operators

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

create table #newproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #newproducts(ProductID,name,size,price)

values(1,’productx’,’small’,20),
(2,’producty’,’small’,40),
(3,’productz’,’large’,50),
(1,’productxy’,’small’,60)
create table #oldproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #oldproducts(ProductID,name,size,price)

values(4,’productq’,’small’,20),
(5,’productk’,’small’,40),
(6,’productz’,’large’,50),
(1,’productxy’,’small’,60),
(1,’productzy’,’small’,80)
select * from #newproducts

select * from #oldproducts
;with oldonly as
(
select * from #oldproducts

except

select * from #newproducts

),

newonly as (select * from #newproducts

except

select * from #oldproducts)
select o.ProductID,o.name as oldname, n.name as newname

from oldonly o inner join newonly n on o.ProductID = n.ProductID

where o.name <> n.name