EXCEPT Clause in SQL Server -

EXCEPT Clause in SQL Server help us to find records that are part of the first SQL set and not included in the second set. When we describe EXCEPT in set operation that will be A without B or A – (A intersect B). A Venn diagram below also describes EXCEPT operator.

For EXCEPT to work each set must be union compatible or corresponding column of both sets must have the same number of columns, same data type, and same order of columns in order for two sets to be union-compatible.
To make it more clear let us take a practical example on Adventure Works database. Let our SET A be all employees with no middle name and SET B be only or sales persons with no middle name. What we want to find is any employee with no middle name and who is not a sales person.

--Employee with no middle name
SELECT FirstName,MiddleName,LastName
FROM HumanResources.Employee
JOIN Person.Person 
ON Person.BusinessEntityID = Employee.BusinessEntityID AND
	MiddleName IS NULL

--SETB
--SalesPerson with no middle name
SELECT FirstName,MiddleName,LastName
FROM Sales.SalesPerson
JOIN Person.Person 
ON Person.BusinessEntityID = SalesPerson.BusinessEntityID AND
	MiddleName IS NULL

;WITH SETA AS( --Employee with no middle name
	SELECT FirstName,MiddleName,LastName
	  FROM HumanResources.Employee
		JOIN Person.Person 
		ON Person.BusinessEntityID = Employee.BusinessEntityID AND
		   MiddleName IS NULL
), SETB AS(  --SalesPerson with no middle name
	SELECT FirstName,MiddleName,LastName
	  FROM Sales.SalesPerson
		JOIN Person.Person 
		ON Person.BusinessEntityID = SalesPerson.BusinessEntityID AND
		   MiddleName IS NULL
)
SELECT FirstName,MiddleName,LastName
FROM SETA
 
EXCEPT 
 
SELECT FirstName,MiddleName,LastName
FROM SETB;

We had 12 employees in SET A and 1 employee in SET B and (SET A EXCEPT SET B) gave us 11 employees.