Click here to Skip to main content
15,120,883 members
Articles / Database Development / SQL Server / SQL Server 2012
Technical Blog
Posted 21 Dec 2014

Stats

9.2K views
5 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
4.57/5 (5 votes)
21 Dec 2014MIT3 min read
How to use the Except Operator in SQLServer

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

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 in the left and not in the right query.

Example

Below is the general format of the EXCEPT operator.

SQL
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:

SQL
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:

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

SQL
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:

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

SQL
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:

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

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
QuestionFormat issues Pin
Nelek5-Mar-15 2:03
protectorNelek5-Mar-15 2:03 
AnswerRe: Format issues Pin
essentialSQL11-Mar-15 2:57
MemberessentialSQL11-Mar-15 2:57 
GeneralRe: Format issues Pin
Nelek12-Mar-15 12:27
protectorNelek12-Mar-15 12:27 
GeneralMy vote of 5 Pin
E. Scott McFadden23-Dec-14 11:02
professionalE. Scott McFadden23-Dec-14 11:02 
Nice and sweet. Thanks to sharing!
GeneralRe: My vote of 5 Pin
essentialSQL19-Jan-15 17:28
MemberessentialSQL19-Jan-15 17:28 
Question999 Pin
Dari2521-Dec-14 20:41
MemberDari2521-Dec-14 20:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.