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.
Read More:
http://technet.microsoft.com/en-us/library/ms188055.aspx