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.

Except Operator

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


Below is the general format of the EXCEPT operator.

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

There are two queries that 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
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 are the query males, the one for females is very similar:

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.

FROM   HumanResources.Employee
WHERE  Gender = 'M'
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

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 except operator.

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


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 subquery.

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

FROM   HumanResources.Employee AS M
WHERE  M.Gender = 'M'
                              FROM   HumanResources.Employee AS F
                              WHERE  F.Gender = 'F')

I colored the subquery in green.  We haven’t talked about sub queries yet, but will in the next series of articles.  In general, the subquery 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 in the set of job titles held by females (the subquery).  If not, then the job title is retained in the results.

NOTE:  These are equivalent to a point.  AS we have learned, NULL isn’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.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

    • Incorrect. The intention is to return jobs which are only done by males and to exclude jobs which are done by any females. If you had a role – “sales assistant”, say – which was done by both sexes then this would not be excluded unless you used the except clause.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}


    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.