Get Ready to Learn SQLServer: 18. How To Use the EXCEPT Operator

How to Use the Except Operator

The EXCEPT operator is used to exclude like rows that are found in one query but not another. It returns rows that are unique to one result. To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible data types.

Visual Example of Except

In this example, the circles represent two queries. The orange circle is the left query; whereas, the blue circle is the right. The area within each circle represents that query’s results.

As you can see, the orange crescent (moon shape) represents the result of the EXCEPT operator. This area represents those rows that are in the left and not in the right query.

Example

Below is the general format of the EXCEPT operator.

SELECT Name, BirthDate FROM Employee
EXCEPT
SELECT Name, BirthDate FROM Customer

There are two queries which are separated by the EXCEPT operator. The top query is commonly called the left query.

The query is valid since both the left and right queries contain the same number of columns and each column is a similar data type; Char and Date respectively.

Contrast this to:

SELECT Name, BirthDate FROM Employee
EXCEPT
SELECT Age, BirthDate, Name FROM Customer

Which is invalid on multiple levels. First, the number of columns isn’t the same. Additionally, the data type for each column is incompatible. For instance, Name, which is a Char column isn’t a compatible data type with Age.

Uses for Except

The except operator is good when you want to find common rows exclusive to one result.

Except Two Tables

Let’s assume we want to find all job titles for positions held by males but not female employees. How could we do this? The first set is to compose the queries to find positions held by males, then to do the same for females.

Here is the query for males, the one for females is very similar:

SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'

To finish, we need to find out which titles are common to only male employees. To do this, we can use the EXCEPT operator.

SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'
EXCEPT
SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'F'

You may be tempted to try and simplify this statement by eliminating the EXCEPT operator all together and use the following:

SELECT JobTitle
FROM   HumanResources.Employee
WHERE  Gender = 'M'
       AND NOT Gender = 'F'

But this won’t simply work. Why? Because the Where clause is evaluated for each row. Logically, this where clause will return all job titles for males.

Order By

To order the result by JobTitle, we can use an ORDER BY clause. Keep in mind this works on the final row set returned by the interest operator.

SELECT   JobTitle
FROM     HumanResources.Employee
WHERE    Gender = 'M'
EXCEPT
SELECT   JobTitle
FROM     HumanResources.Employee
WHERE    Gender = 'F'
ORDER BY JobTitle

Equivalence

The EXCEPT operator was just recently added to SQL Server. Before its introduction to the language, you had to mimic the EXCEPT behavior using a sub query.

Below is the equivalent statement to find job titles only held by Males:

SELECT DISTINCT M.JobTitle
FROM   HumanResources.Employee AS M
WHERE  M.Gender = 'M'
       AND M.JobTitle NOT IN (SELECT F.JOBTITLE
                              FROM   HumanResources.Employee AS F
                              WHERE  F.Gender = 'F')

I colored the sub query in green. We haven’t talked about sub queries yet, but will in the next series of articles. In general, the sub query is run once for each result returned from the main query. In this example, once we select a job title that is held by a male (the main query) we then do another query asking whether that job title is in the set of job titles held by females (the sub query). If not, then the job title is retained in the results.

NOTE: These are equivalent to a point. As we have learned, NULL aren’t values, therefore; NULL = NULL is always false. Given this, the INNER JOIN will fail to match on joins; however, the EXCEPT operator does match NULLS.