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