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