I stumbled across an interesting SQL command the other day--the EXCEPT statement. The EXCEPT statement returns distinct values from query A where query A records do not match query B records. To explain how it works, lets work with two tables:
CREATE TABLE [Customers]
(
[CustomerID] [int] NOT NULL,
[FirstName] [nvarchar](50),
[LastName] [nvarchar](50),
[Email] [varchar](100)
)
CREATE TABLE [CustomerAttributes]
(
[CustomerID] [int] NOT NULL,
[Attribute] [int] NOT NULL
)
The Customers table is responsible to hold customer data and the CustomerAttributes table assigns multiple attributes to the customers. Lets say that attribute 5 is "No Mail" indicating that this customer does not wish to receive email. In order to make sure customers do not receive email if they have the "No Email" attribute, we would need to create something like this:
SELECT DISTINCT
c.CustomerID,
c.FirstName,
c.LastName,
c.Email
FROM
Customers AS c
LEFT OUTER JOIN
(SELECT CustomerID FROM CustomerAttributes WHERE Attribute = 5) AS a
ON
c.CustomerID = a.CustomerID
WHERE
(a.CustomerID IS NULL)
Using the EXCEPT statement, we can create the following alternative SQL:
SELECT
CustomerID,
FirstName,
LastName,
Email
FROM
Customers
EXCEPT
SELECT
c.CustomerID,
c.FirstName,
c.LastName,
c.Email
FROM
Customers AS c
INNER JOIN
CustomerAttributes AS a
ON
c.CustomerID = a.CustomerID
WHERE a.Attribute = 5
There are a couple of constraints involved in using the EXCEPT statement. There must be an equal number of fields returned in both queries and the data types of these fields must match.
The EXCEPT statement can be used to compare more than one set of queries. In this situation, query A and query B are compared and a result set is created, then this result set is compared with query C and so on. In the end, you will have a result set with distinct values filtered of all records that match any of the EXCEPT queries provided.