EXCEPT and INTERSECT – SET Operator In SQL Server

Posted May 28, 2019 by Vishwanath Dalvi in Database, SQL Server

SQL Server supports ANSI SET operators. Including UNION, UNION ALL, EXCEPT and INTERSECT. Generally, SET operators used to combine multiple query result sets into a single result set. In an early post on tech-recipes, we have learned the use of UNION and UNION ALL operators.

In this tech-recipe article, let’s do a walk-through with examples for EXCEPT & INTERSECT operators in SQL Server.

Difference Between SET Operator & SQL Joins

SET operators are different from SQL Joins. According to the concepts, SQL join combines rows from one or more table based on a common column. Whereas, SET operators combine results of multiple queries together in single result set.

INTERSECT – Set Operator

INTERSECT operator combines only common distinct rows from multiple result sets. If a row is available in both the input result sets then it’s returned in the final result set. If a row is available in the first result set but not in another then it is discarded.

Furthermore, duplicate rows are removed. A single instance of a duplicated row is returned. Most noteworthy, NULLs are treated equally with INTERSECT operator, therefore they’re combined.

EXCEPT – Set Operator

EXCEPT operator discards the rows from the first result set which are not available in the second result set. As a rule, rows from the first result set not matching with second result set are discarded. Besides, duplicate rows will be dropped in the final result set using EXCEPT operator.

In other variation of SQL, MINUS is similar to EXCEPT operator in all way.

Examples


Before running the following posted examples, let’s create sample data to understand INTERSECT & EXCEPT operator thoroughly.

USE tempdb;
GO
IF OBJECT_ID(N'dbo.Students', N'U') IS NOT NULL
DROP TABLE dbo.Students;
GO

CREATE TABLE dbo.Students
(
ID		INT,
Name	VARCHAR(255),
Grade   CHAR(1)
);

INSERT INTO dbo.Students VALUES 
 (1, 'Vish' ,'C')
,(2, 'Shail','A')
,(3, 'Sang' ,'B')
,(4, 'Ravi' ,'A')
,(5, 'Aksh' ,'B')
,(5, 'Aksh' ,'B');

IF OBJECT_ID(N'dbo.FootballTeam', N'U') IS NOT NULL
DROP TABLE dbo.FootballTeam;
GO

CREATE TABLE dbo.FootballTeam
(
TeamName	VARCHAR(255),
StudentId	INT
);

INSERT INTO dbo.FootballTeam VALUES 
 ('Mumba', 1) 
,('Mumba', 3) 
,('Pun',   5)
,('Pun',   5);


SELECT * FROM Dbo.Students;

ID          Name     Grade
----------- -------- -----
1           Vish     C
2           Shail    A
3           Sang     B
4           Ravi     A
5           Aksh     B
5           Aksh     B

(6 rows affected)

SELECT * FROM Dbo.FootballTeam;

TeamName    StudentId
----------- -----------
Mumba       1
Mumba       3
Pun         5
Pun         5

(4 rows affected)


1.

Simple EXCEPT Operator

In the following example, EXCEPT operator takes the first query result set and discards the rows which are not available in the second query result. Therefore, 2 & 4 student Ids from dbo.Students table are returned because they’re not present in dbo.FootballTeam table.

SELECT Id FROM dbo.Students
EXCEPT
SELECT StudentId FROM dbo.FootballTeam;


Id
-----------
2
4

(2 rows affected)


2.

Simple INTERSECT Operator

Here, the INTERSECT operator combines common rows from the first and second query result set. Therefore, 1, 3, 5 student Ids are displayed in the result set as these IDs are present in dbo.Students as well as in dbo.FootballTeam.

SELECT Id FROM dbo.Students
INTERSECT
SELECT StudentId FROM dbo.FootballTeam;


Id
-----------
1
3
5

(3 rows affected)


3.

EXCEPT & INTERSECT With Duplicate Rows

Table dbo.Students and dbo.FootBallTeam has duplicate rows for student id 5. If we run EXCEPT and INTERSECT examples then duplicate values are not returned. Student Id 5 is returned only once even though it’s duplicated.

SELECT Id FROM dbo.Students
EXCEPT
SELECT StudentId FROM dbo.FootballTeam;

SELECT Id FROM dbo.Students
INTERSECT
SELECT StudentId FROM dbo.FootballTeam;


Id
-----------
2
4

(2 rows affected)

Id
-----------
1
3
5

(3 rows affected)


4.

EXCEPT & INTERSECT With NULLs

Nulls are treated oppositely with SET operators. They are treated equally. Thus, if we’ve NULLs in both result sets and using INTERSECT will return NULLs considering their presence is common in both the result sets. Here we can note this behaviour.

In below example, NULLs are displayed along with 1,2 as these rows are common between two result sets.

SELECT * FROM (VALUES (NULL, NULL), (2,3), (1,2)) as Test1(Col1,Col2)
INTERSECT
SELECT * FROM (VALUES (NULL, NULL), (1,2)) as Test2(Col1,Col2);


Col1        Col2
----------- -----------
NULL        NULL
1           2

(2 rows affected)


Similarly for EXCEPT operator, If NULLs are present in the first result set and not in second. Therefore, NULLs are shown in the result set.

SELECT * FROM (VALUES (NULL, NULL), (2,3), (1,2)) as Test1(Col1,Col2)
EXCEPT
SELECT * FROM (VALUES (1,2)) as Test2(Col1,Col2);


Col1        Col2
----------- -----------
NULL        NULL
2           3

(2 rows affected)


5.

Alternative to EXCEPT Operator In SQL Server

Above all, we can use NOT IN and NOT EXISTS clause as an alternative to EXCEPT operator. In constrast, the former option gives us the liberty to include columns which are not part of the comparison. In below examples re-writing alternate query for EXCEPT operator.

SELECT Id FROM dbo.Students
EXCEPT
SELECT StudentId FROM dbo.FootballTeam;

SELECT Id, Name, Grade FROM dbo.Students
WHERE ID NOT IN (SELECT StudentId FROM dbo.FootballTeam);

SELECT Id, Name, Grade FROM dbo.Students as St
WHERE  NOT EXISTS (SELECT StudentId FROM dbo.FootballTeam WHERE StudentId = St.ID);


Id
-----------
2
4

(2 rows affected)

Id          Name                                                                                                                                                                                                                                                            Grade
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----
2           Shail                                                                                                                                                                                                                                                           A
4           Ravi                                                                                                                                                                                                                                                            A

(2 rows affected)

Id          Name                                                                                                                                                                                                                                                            Grade
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----
2           Shail                                                                                                                                                                                                                                                           A
4           Ravi                                                                                                                                                                                                                                                            A

(2 rows affected)


6.

Alternative to INTERSECT Operator In SQL Server

Also using IN and EXISTS clause as an alternative to INTERSECT operator to achieve similar behaviour. Together with a DISTINCT clause to remove duplicates.

SELECT Id FROM dbo.Students
INTERSECT
SELECT StudentId FROM dbo.FootballTeam;

SELECT DISTINCT Id FROM dbo.Students
WHERE Id IN (SELECT StudentId FROM dbo.FootballTeam);

SELECT DISTINCT Id FROM dbo.Students as St
WHERE EXISTS (SELECT StudentId FROM dbo.FootballTeam WHERE StudentId = St.ID);


Id
-----------
1
3
5

(3 rows affected)

Id
-----------
1
3
5

(3 rows affected)

Id
-----------
1
3
5

(3 rows affected)


6.

EXCEPT & INTERSECT Operator In Graphical Execution Plan

Execution plan, query optimizer’s attempt to execute query in efficient way. EXCEPT and INTERSECT execution plan includes Left Anti Semi Join to combine two result sets. Including distinct sort operator to remove duplicates.

EXCEPT INTERSECT Operator IN SQL Server

Summary

As a result we have learned to use EXCEPT and INTERSECT operator in SQL Server. Likewise, SET operators allows us to combine multiple result sets. Similarly the alternative queries for SET operators using NOT EXISTS and NOT IN clause. Visit Tech-recipes database archive.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply