EXCEPT Operator in SQL Server

Back to: SQL Server Tutorial

EXCEPT Operator in SQL Server with Examples

In this article, I am going to discuss the EXCEPT Operator in SQL Server with Examples. The EXCEPT operator belongs to the SET Operator category. Please read our previous article where we discussed the basics of SET Operators in SQL Server as well as we also discussed the UNION and UNION ALL SET Operators in detail.

What is the EXCEPT operator in SQL Server?

The EXCEPT operator in SQL Server is used to return unique rows from the left query which isn’t present in the right query’s results. If this is not clear at the moment then don’t worry we will try to understand this with some examples.

Understanding the EXCEPT operator with examples.

Let us understand the EXCEPT Operator with an example. We are going to use the following “EmployeeIndia” and “EmployeeUK” tables to understand this operator.

Please use the below SQL Script to create Database EmployeeDB, tables EmployeeIndia and EmployeeUK and populate these two tables with the required 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

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

Example:

The following SQL Query will return the unique rows from the left query (the select statement before the EXCEPT operator) that is not present in the right query (the select statement after the EXCEPT operator).

SELECT ID, Name, Gender, Department FROM EmployeeIndia
EXCEPT
SELECT ID, Name, Gender, Department FROM EmployeeUK

Results:

To retrieve all of the rows from Table EmployeeUK that do 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 Operator in SQL Server on a single table:

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

Please use the below SQL Script to create and populate the Employees table with the required test data.

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

Order By clause should be used only once after the last select statement

Result:

Difference between EXCEPT and NOT IN Operator SQL server

Now, you may have one question on your mind i.e. what is the difference between EXCEPT and NOT IN Operator in SQL Server. Let’s understand the difference between EXCEPT and NOT IN operators in SQL Server with one example. We are going to use the following two tables (TableA and TableB).

Please use the below SQL Script to create the necessary tables with the required test data.

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

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

Example: Using EXCEPT Operator

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:

Example: Using NOT IN Operator

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

SELECT ID, Name, Gender, Department FROM TableA Where ID NOT IN (Select ID from TableB)

Result:

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

The 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. To understand this, 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:

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:

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

The NOT IN operator 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 am going to discuss the INTERSECT Operator in SQL Server with some examples. Here, in this article, I try to explain the EXCEPT Operator in SQL Server with Examples. I hope you enjoy this EXCEPT Operator in SQL Server with Examples article. I would like to have your feedback. Please post your feedback, question, or comments about this EXCEPT Operator in SQL Server with Examples article.