Except and Intersect in SQL Server

EXCEPT and INTERSECT operators were introduced in SQL Server 2005. Both operator works on two result-sets.

Basic rules for combining two result-sets

• No. of columns in both result-set should be equal
• The order of columns should be same
• Corresponding data type of each column in each select should be compatible

Syntax

SELECT col1, col2 FROM tab1
EXCEPT
SELECT col1, col2 FROM tab2

EXCEPT returns distinct rows from the first result-set which not exists in second result-set.

INTERSECT returns rows which exists in both result-set

Example

declare @tab1 table (col1 int, col2 varchar(10))
insert into @tab1
select 1, 'val1' union all
select 1, 'val2' union all
select 2, 'val1'

declare @tab2 table (col1 int, col2 varchar(10))
insert into @tab2
select 1, 'val1' union all
select 1, 'val3' union all
select 3, 'val1'

select col1, col2 from @tab1
except
select col1, col2 from @tab2

select col1, col2 from @tab1
intersect
select col1, col2 from @tab2

OUTPUT

Look at the Output

• First output using EXCEPT where we are getting 2 rows which exist in table 1 but not in table 2
• Second output using INTERSECT where we are getting 1 row which exists in both tables.