SQL Server INTERSECT and EXCEPT Operator

Multiple SQL Queries may return duplicate values in recordsets from each query result. There could be situations when a single resultset is needed to be returned by combining multiple SQL queries. In SQL Server, there are many SET operators like UNION, EXCEPT, and INTERSECT to merge multiple results of select statements to a single result set. The INTERSECT operator is one of them which is used to filter distinct values.

Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. In SQL Server, various operators allow you to perform different types of set operations on your data. Two such operators are EXCEPT and INTERSECT, which are used to compare and combine the results of two queries.

INTERSECT Operator in SQL Server

The INTERSECT operator is one of the Set Operators in SQL Server which is used to filter out duplicates and return only distinct rows of data. The INTERSET operator combines two or more SELECT statements and returns a single dataset with common and distinct data between the SELECT statements on the right and the left of the INTERSECT operator.

INTERSECT Syntax:

{ <query_specification> | ( <query_expression> ) }

{ INTERSECT }

{ <query_specification> | ( <query_expression> ) }

• query_specification or query_expression is a SELECT statement with or without a where clause.
• INTERSECT is the operator used to filter distinct or unique data common to the queries specified.

EXCEPT Operator

EXCEPT keyword is an operator that is used to retrieve distinct rows from the left query result set that are not present in the right query result set in a given query. It can be useful in finding the difference between two sets of data. The syntax for the EXCEPT operator is given as follows

Syntax:

SELECT column1, column2, …

FROM table1

EXCEPT

SELECT column1, column2, …

FROM table2;

Examples of SQL Server INTERSECT and EXCEPT Operator

There are 3 tables used in the examples below and the queries to create tables and insert data are given below:

CREATE TABLE [dbo].[Employees](
[EmployeeID] [int] NOT NULL  IDENTITY(1,1),
[EmployeeName] [varchar](50) NULL,
[Technology] [varchar](100) NULL
)

CREATE TABLE [dbo].[Managers](
[ManagerID] [int] NOT NULL IDENTITY(1,1),
[EmployeeID] [int] NULL,
[ManagerName] [varchar](50) NULL,
[Role] [varchar](100) NULL
)

CREATE TABLE [dbo].[Projects](
[ProjectID] [int] NOT NULL IDENTITY(1,1),
[EmployeeID] [int] NULL,
[ProjectName] [varchar](50) NULL
)

Adding values to the Employees table

-- Employees table data
INSERT INTO Employees VALUES 
('John','.NET'), 
('Jane','Java'), 
('Bob','UI/UX'), 
('Alice','.NET'),
('Ben','Java'),
('Mark','Java'),
('Rajesh','MBA'),
('Sunil','.NET'),
('Raja','.NET'),
('Mohan','.NET');

Adding values to the Managers table.

--Managers table data
INSERT INTO Managers VALUES 
(2,'Jane','Project Manager'), 
(4,'Alice','Product Manager'),
(5,'Ben', 'Manager'),
(6,'Mark','VP'),
(8,'Sunil', 'MBA')

Adding values to the Projects table.

-- Projects data
INSERT INTO Projects VALUES 
(1, 'eSmart'),
(2, 'eSmart'),
(3,'eHealth'),
(4,'eHealth'),
(5,'eHealth'),
(6,'eSmart'),
(7,'eHealth'),
(8, 'eSmart')

Example 1: Identifying Employees Who Also Serve as Managers

SELECT EmployeeID, EmployeeName FROM EmployeeDetails
INTERSECT
SELECT EmployeeID, ManagerName FROM ManagerDetails

Output:

Intersect Example 1

In the above example, there are two tables namely Employees and Managers. Using the INTERSECT operator the resultant dataset will return data of employees who are managers, meaning the names which exists in both Employees and Managers tables.

Example 2: Identifying Employees with Java Technology Who Also Serve as Managers

SELECT EmployeeID, EmployeeName FROM Employees where Technology='Java'
INTERSECT
SELECT EmployeeID,ManagerName FROM Managers;

Output:

Intersect Example 2

The above example has where clause combining 2 SQL Queries, to all employees with Technology ‘Java‘ and are managers.

Example 3: We use EXCEPT Operator to Find the employees in the Employee Table that do not have any Project Assignments

-- Example using EXCEPT operator to find employees not assigned to any projects
SELECT EmployeeID, EmployeeName, Technology
FROM Employees
EXCEPT
SELECT e.EmployeeID, e.EmployeeName, e.Technology
FROM Employees e
JOIN Projects p ON e.EmployeeID = p.EmployeeID;

Output:

EmployeeID | EmployeeName | Technology
-----------|--------------|------------
7          | Rajesh       | MBA
10         | Mohan        | .NET

In the example below, we use EXCEPT operator to find the employees in the Employee table that do not have any project assignments. In the Projects table, we use JOIN to find the employees that have project assignments.

Conclusion

In SQL Server, the INTERSECT and EXCEPT operators are used to get the common rows of two SELECT statements. The first SELECT statement retrieves the rows that are common to both SELECT statements. The second SELECT statement retrieves different rows that are not common to the two SELECT statements. These operators help to compare and analyze the data between two pairs of results in an SQL query.