How to use INTERSECT and EXCEPT Set Operators

Problem: How to know what data is common in both the queries without using Joins.

Solution: EXCEPT and INTERSECT

Returns distinct rows by comparing the results of two queries.

EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.

INTERSECT returns distinct rows that are output by both the left and right input queries operator.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

The number and the order of the columns must be the same in all queries.

The data types must be compatible.

If you are familiar with set theory

INTERSECT is also (A intersection B)

 EXCEPT is only A or (A - (A intersection B))

Example:

In this article I am going to show you how we can use INTERSECT and EXCEPT.

select  * from dbo.TableA

select  * from dbo.TableB

select  * from dbo.TableA

INTERSECT

select  * from dbo.TableB

select  * from dbo.TableA

EXCEPT

select  * from dbo.TableB

OUTPUT: