EXCEPT is available in SQL Server starting with SQL Server 2008. EXCEPT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left (first) input query that aren’t output by the right (last) input query. The data and tables are sourced from Philip Burton’s SQL course at Udemy.com. Contrast how EXCEPT works with UNION and INTERSECT.

Consider the following t-SQL code.

select * from tblDepartment;

select * -- create a new table
into tblDepartmentNew
from tblDepartment  -- has 5 rows

delete from tblDepartmentNew where Department = 'HR';

select * from tblDepartment -- has 5 rows
except 
select * from tblDepartmentNew  -- has 4 rows
-- we should only see the HR department

Here are the results from SSMS.

So EXCEPT says give me all of the rows in the first table and then delete from that result set all of the matching rows in the second table. What if there was a row in the second table that was not in the fists table? That row simply gets ignored and is not part of the result set.

Duplicates

What if we have duplicates in the first table. We have added some data to the first table. There is a duplicate, namely HR, Bryn. Notice that the results eliminate the duplicate rows from the first table.

select * from tblDepartment;

select * -- create a new table
into tblDepartmentNew
from tblDepartment  -- has 7 rows with 2 sets of duplicates.

delete from tblDepartmentNew where Department = 'HR';

select * from tblDepartmentNew

select * from tblDepartment -- has 5 rows
except
select * from tblDepartmentNew;  -- has 4 rows
-- we should only see the HR department but the question is
-- do we get both rows? No we don't.

with cte as (
select * from tblDepartment -- has 5 rows
except
select * from tblDepartmentNew  -- has 4 rows
)
select * 
into tblDepartmentResult
from cte;

Here are the results from SSMS.