SQL INTERSECT And EXCEPT Operator

Introduction

These two operators EXCEPT and INTERSECT have been introduced since SQL Server 2005 and these two returns distinct or unique values by comparing the results of two queries.

Moreover, these operators do have rules for combining result sets just like other operators like UNION and we’ll also discuss it.

That’s why in this article we’ll be showing how we can use these two operators and understand their differences.

What is INTERSECT Operator?

Fundamentally, INTERSECT operator combines two or more queries and returns unique rows or records that are the outcomes of both left and right queries.

Then it removes duplicate records as the final result set.

INTERSECT Syntax

--INTERSECT Syntax 
query_expression 
INTERSECT 
query_expression

It is similar to the UNION operator, that’s why both queries must have the same number and order of columns, and the column data type should be the same.

Furthermore, if these rules aren’t followed an error will occur like failing conversion of data and an equal number of expressions error.

INTERSECT Operator Example

Before we can see examples, let’s try to create a table first with some data in it.

DECLARE @TBLDOGS TABLE (Id int, DogBreed nvarchar(50)) 
DECLARE @TBLDOG_OWNERS TABLE (Id int, OwnersName nvarchar(50), 
DogBreed nvarchar(50)) 

INSERT INTO @TBLDOGS (Id, DogBreed) 
VALUES 
(1, 'German Shepherd'),
(2, 'Labrador Retriever'),
(3, 'Golden Retriever'),
(4, 'Poodle'),
(5, 'French Bulldog')

INSERT INTO @TBLDOG_OWNERS (Id, OwnersName, DogBreed)
VALUES 
(1, 'Bruce Wayne', 'German Shepherd'),
(2, 'Lex Luthor', 'Golden Retriever'),
(3, 'Vandal Savage', 'Dobermann'),
(4, 'Maxwell Lord', 'Dachshund'),
(5, 'Simon Stagg ', 'Poodle'),		         
(6, 'Elon Musk', 'German Shepherd')

SELECT * FROM @TBLDOGS
SELECT * FROM @TBLDOG_OWNERS

Output

OK, so everything has been set up. Let’s show how we can use the INTERSECT operator.

SELECT DogBreed FROM @TBLDOGS
INTERSECT 
SELECT DogBreed FROM @TBLDOG_OWNERS

Output

As you can see, in our example we have two tables @TBLDOGS and @TBLDOG_OWNERS.

Going to the data of the two tables, @TBLDOGS have German Shepherd, Labrador Retriever, Golden Retriever, Poodle, and French Bulldog.

While @TBLDOG_OWNERS have German Shepherd, Golden Retriever, Dobermann, Dachshund, Poddle, and German Shepherd.

The intersection of the two tables resulted in a German Shepherd, Golden Retriever, and Poodle.

What is EXCEPT Operator?

Fundamentally, EXCEPT operator subtracts a query result set from another query result set.

In other words, it returns unique rows or records from the left query that doesn’t appear or are not found on the right query.

EXCEPT Syntax

--EXCEPT Syntax 
query_expression 
EXCEPT
query_expression 

Moreover, both queries must have the same number and order of columns, and the column data type should be the same.

Furthermore, if these rules aren’t followed an error will occur like failing conversion of data and an equal number of expressions error.

EXCEPT Operator Example

Data about dogs from the previous example, we’re still going to use here.

To lessen confusion I’ll be posting again the data sample of dogs.

DECLARE @TBLDOGS TABLE (Id int, DogBreed nvarchar(50)) 
DECLARE @TBLDOG_OWNERS TABLE (Id int, OwnersName nvarchar(50), 
DogBreed nvarchar(50)) 

INSERT INTO @TBLDOGS (Id, DogBreed) 
VALUES 
(1, 'German Shepherd'),
(2, 'Labrador Retriever'),
(3, 'Golden Retriever'),
(4, 'Poodle'),
(5, 'French Bulldog')

INSERT INTO @TBLDOG_OWNERS (Id, OwnersName, DogBreed)
VALUES 
(1, 'Bruce Wayne', 'German Shepherd'),
(2, 'Lex Luthor', 'Golden Retriever'),
(3, 'Vandal Savage', 'Dobermann'),
(4, 'Maxwell Lord', 'Dachshund'),
(5, 'Simon Stagg ', 'Poodle'),		         
(6, 'Elon Musk', 'German Shepherd')

SELECT * FROM @TBLDOGS
SELECT * FROM @TBLDOG_OWNERS

Output

OK, so everything has been set up. Let’s show how we can use the EXCEPT operator.

SELECT DogBreed FROM @TBLDOGS
EXCEPT
SELECT DogBreed FROM @TBLDOG_OWNERS

SELECT DogBreed FROM @TBLDOG_OWNERS
EXCEPT
SELECT DogBreed FROM @TBLDOGS

Output

Going to the data of the two tables, the EXCEPT operator resulted differently and it makes a significant difference because of what is on the left table.

Moreover, as we change the left table we have different results.

Moreover, from the example above the first result, the left query is the @TBLDOGS while the right query is the @TBLDOG_OWNERS.

While the second result, the left query is the @TBLDOG_OWNERS while the right query is the @TBLDOGS.

Let’s see the difference between EXCEPT and INTERSECT operators in the next section.

Difference Between INTERSECT and EXCEPT Operators

Remember that the Intersect operator uses an intersection that takes rows from both result sets that are common in both.

While the Except operator takes rows from the left result set but excludes the right result set that isn’t found.

Conclusion

In this article, we have learned how to use the SQL Server’s INTERSECT and EXCEPT operators and have seen some examples to understand these operators.

Moreover, we didn’t forget to show the difference between the two.

I hope you have enjoyed this article, as I enjoyed it while writing.

Till next time, Happy programming! Cheers! And Thank you!