Except and intersect offer a way to compare the result of two select statements and either return matching records (INTERSECT ) or only records that are contained in the select statement to the left of the operator (EXCEPT).
In the below examples we are using the Adventure Works database which is available for download from CodePlex here.
When using except and intersect queries the column order, number and data type need to be the same in both select statements otherwise an error will be returned. Only columns that are defined in the select statement are used for the comparison operation.
The EXCEPT operator is used to return all records from the left side select statement where they do not exist in the right side result set.
For example if we want to return all the products that haven’t appeared on a purchase order we could use the following query,
SELECT ProductID, name FROM Production.Product EXCEPT SELECT o.ProductID , p.Name FROM Purchasing.PurchaseOrderDetail o join Production.Product p ON p.ProductID = o.ProductID
The above query would return the productID and product name columns.
If we wanted to return all products that had been on a purchase order we could use the INTERSECT operator which will return only equal records as per below. The return set is also distinct so you wont see any duplicate rows in the results.
SELECT ProductID, name FROM Production.Product INTERSECT SELECT o.ProductID , p.Name FROM Purchasing.PurchaseOrderDetail o join Production.Product p ON p.ProductID = o.ProductID
There is a lot more detail available on except and intersect in the MSDN definition of the operators which can be found here