EXCEPT Operator

The EXCEPT Operator in SQL Server

SET operators are mainly used to combine the result of more than 1 select statement and return a single result set to the user. In this article, I will discuss EXCEPT operator in SQL Server.

The set operators work on complete rows of the queries, so the results of the queries must have the same column name, same column order and the types of columns must be compatible. There are the following 4 set operators in SQL Server:

  1. UNION: Combine two or more result sets into a single set, without duplicates.
  2. UNION ALL: Combine two or more result sets into a single set, including all duplicates.
  3. INTERSECT: Takes the data from both result sets which are in common.
  4. EXCEPT: Takes the data from the first result set, but not in the second result set (i.e. no matching to each other)
Rules on Set Operations:
  1. The result sets of all queries must have the same number of columns.
  2. In every result set the data type of each column must be compatible (well matched) to the data type of its corresponding column in other result sets.
  3. In order to sort the result, an ORDER BY clause should be part of the last select statement. The column names or aliases must be found out by the first select statement.

In this article, I will discuss EXCEPT operator in SQL Server. In our last article, we discussed UNION and UNION ALL operators with some example. So Please read UNION and UNION ALL operators article of this article series before proceeding to this article.

The EXCEPT operator:

It returns unique rows from the left query that isn’t present in the right query’s results.

Let us understand this with an example.

Use below script to create Database, tables and populate the tables with some test data.

CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

CREATE TABLE EmployeeIndia
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO EmployeeIndia VALUES(1, 'Pranaya', 'Male','IT')
INSERT INTO EmployeeIndia VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO EmployeeIndia VALUES(3, 'Preety', 'Female','HR')
INSERT INTO EmployeeIndia VALUES(4, 'Subrat', 'Male','HR')
INSERT INTO EmployeeIndia VALUES(5, 'Anurag', 'Male','IT')
GO
Fetch the records:

SELECT * FROM EmployeeIndia

EXCEPT Operator in SQL Server

CREATE TABLE EmployeeUK
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO EmployeeUK VALUES(1, 'James', 'Male','IT')
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO EmployeeUK VALUES(3, 'Sara', 'Female','HR')
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Male','HR')
INSERT INTO EmployeeUK VALUES(5, 'Pam', 'Female','HR')
GO
Fetch the records:

SELECT * FROM EmployeeUK

EXCEPT Operator in SQL Server

Notice that the following query returns the unique rows from the left query that is not present in the right query’s results.

SELECT ID, Name, Gender, Department FROM EmployeeIndia

EXCEPT

SELECT ID, Name, Gender, Department FROM EmployeeUK

Results:

EXCEPT Operator in SQL Server

To retrieve all of the rows from Table EmployeeUK that does not exist in Table EmployeeIndia, reverse the two queries as shown below.

SELECT ID, Name, Gender, Department FROM EmployeeUK

EXCEPT

SELECT ID, Name, Gender, Department FROM EmployeeIndia

Result:

EXCEPT Operators in SQL Server

You can also use EXCEPT operator on a single table. Let’s use the following Employees table for this example. 

-- SQL script to create Employees table
Create table Employees
(
    Id int identity primary key,
    Name nvarchar(100),
    Gender nvarchar(10),
    Salary int
)
Go

Insert into Employees values ('Mark', 'Male', 52000)
Insert into Employees values ('Mary', 'Female', 55000)
Insert into Employees values ('Steve', 'Male', 45000)
Insert into Employees values ('John', 'Male', 40000)
Insert into Employees values ('Sara', 'Female', 48000)
Insert into Employees values ('Pam', 'Female', 60000)
Insert into Employees values ('Tom', 'Male', 58000)
Insert into Employees values ('George', 'Male', 65000)
Insert into Employees values ('Tina', 'Female', 67000)
Insert into Employees values ('Ben', 'Male', 80000)
Go
Fetch the records:

Select * from Employees

EXCEPT Operators in SQL Server

Order By clause should be used only once after the last select statement
Select Id, Name, Gender, Salary
From Employees
Where Salary >= 50000
Except
Select Id, Name, Gender, Salary
From Employees
Where Salary >= 60000
order By Name

Result:

EXCEPT Operators in SQL Server

Difference between except and not in SQL server

Let’s understand the difference between EXCEPT and NOT IN operators in SQL Server with one example

Use below script to create the necessary tables with some test data that we are going to use in this example.

CREATE TABLE TableA
(
  ID INT,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO TableA VALUES(1, 'Pranaya', 'Male','IT')
INSERT INTO TableA VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO TableA VALUES(3, 'Preety', 'Female','HR')
GO
Fetch the records:

SELECT * FROM TableA

EXCEPT Operators in SQL Server

CREATE TABLE TableB
(
  ID INT,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO TableB VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO TableB VALUES(3, 'Preety', 'Female','HR')
GO
Fetch the records:

SELECT * FROM TableB

EXCEPT Operators in SQL Server

The following query returns the rows from the left query that aren’t present in the right query’s results. 

SELECT ID, Name, Gender, Department FROM TableA

EXCEPT

SELECT ID, Name, Gender, Department FROM TableB

Result:

EXCEPT Operators in SQL Server

The same result can also be achieved using NOT IN operator.

SELECT ID, Name, Gender, Department FROM TableA

Where ID NOT IN (Select ID from TableB)

Result:

EXCEPT Operators in SQL Server

So, what is the difference between EXCEPT and NOT IN operators in SQL Server?

EXCEPT operator filters duplicate rows and return only DISTINCT rows from the left query that aren’t in the right query’s results, whereas NOT IN does not filter the duplicates rows.

Insert the following row into TableA

INSERT INTO TableA VALUES(1, ‘Pranaya’, ‘Male’,’IT’)

Now execute the following EXCEPT query. Notice that we get only the DISTINCT rows in the result set.

SELECT ID, Name, Gender, Department FROM TableA

EXCEPT

SELECT ID, Name, Gender, Department FROM TableB

Result:

EXCEPT Operators in SQL Server

Now execute the following query. Notice that the duplicate rows are not filtered in the result set.

SELECT ID, Name, Gender, Department FROM TableA

Where ID NOT IN (Select ID from TableB)

Result:

EXCEPT Operators in SQL Server

EXCEPT operator expects the same number of columns in both the queries, whereas NOT IN, compares a single column from the outer query with a single column from the subquery.

In the following example, the number of columns is different.

SELECT ID, Name, Gender, Department FROM TableA

EXCEPT

SELECT ID, Name, Gender FROM TableB

The above query would produce the following error.

Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

NOT IN, compares a single column from the outer query with a single column from the subquery

In the following example, the subquery returns multiple columns

SELECT ID, Name, Gender, Department FROM TableA

Where ID NOT IN (Select ID, Name from TableB)

The above query would produce the following error.

Msg 116, Level 16, State 1, Line 2

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

In the next article, I will discuss INTERSECT Operator in SQL Server with some examples.

SUMMARY

In this article, I try to explain the EXCEPT Operator in SQL Server step by step with some examples. I hope this article will help you in your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *