INTERSECT and EXCEPT

INTERSECT and EXCEPT are operators within SQL Server that takes data from two queries and compares the results.

INTERSECT returns the distinct values from both the left query and right query.

EXCEPT is different as it returns only the data thats in the left query and not in the right query.

Similar to UNION and UNION all, the INTERSECT and EXCEPT operators require the same number of columns are returned from both the left and right queries. It’s also required that the data types within these columns match.

First we’ll create some sample data and then we’ll begin using them:

Sample Data

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

IFOBJECT_ID('tempdb..#LeftSample')ISNOTNULL

BEGIN

DROPTABLE#LeftSample

END;

IFOBJECT_ID('tempdb..#RightSample')ISNOTNULL

BEGIN

DROPTABLE#RightSample

END;

GO

CREATETABLE#LeftSample

(

MovieIDINTIDENTITY(1,1)NOTNULL,

MoviesVARCHAR(100)NOTNULL

);

CREATETABLE#RightSample

(

MovieIDINTIDENTITY(1,1)NOTNULL,

MoviesVARCHAR(100)NOTNULL

);

GO

SETNOCOUNTON;

INSERTINTO#LeftSampleVALUES('Star Wars');

INSERTINTO#LeftSampleVALUES('Lord of the Rings');

INSERTINTO#LeftSampleVALUES('Star Trek');

INSERTINTO#LeftSampleVALUES('Iron Man');

INSERTINTO#LeftSampleVALUES('Alien');

INSERTINTO#LeftSampleVALUES('Indianna Jones');

INSERTINTO#LeftSampleVALUES('A Nightmare Before Christmas');

INSERTINTO#LeftSampleVALUES('Serenity');

INSERTINTO#LeftSampleVALUES('Dune');

INSERTINTO#LeftSampleVALUES('Blade Runner');

INSERTINTO#LeftSampleVALUES('Back to the Future');

INSERTINTO#LeftSampleVALUES('Weird Science');

INSERTINTO#RightSampleVALUES('Star Wars');

INSERTINTO#RightSampleVALUES('Lord of the Rings');

INSERTINTO#RightSampleVALUES('Guardians of the Galaxy');

INSERTINTO#RightSampleVALUES('Batman');

INSERTINTO#RightSampleVALUES('Alien');

INSERTINTO#RightSampleVALUES('Indianna Jones');

INSERTINTO#RightSampleVALUES('Labyrinthe');

INSERTINTO#RightSampleVALUES('Serenity');

INSERTINTO#RightSampleVALUES('Short Circuit');

INSERTINTO#RightSampleVALUES('Blade Runner');

INSERTINTO#RightSampleVALUES('Back to the Future 2');

INSERTINTO#RightSampleVALUES('Teen Wolf');

GO

Intersect:

The following INTERSECT statement will return the distinct rows from both the left query and the right query.

Intersect

Transact-SQL

1

2

3

4

5

SELECT*FROM#LeftSample

INTERSECT

SELECT*FROM#RightSample;

Results:

1

2

3

4

5

6

7

8

MovieID Movies

------------------------------

1Star Wars

2Lord of the Rings

5Alien

6Indianna Jones

8Serenity

10Blade Runner

EXCEPT

The EXCEPT operator will returns data from the left query that isn’t in the right query.

EXCEPT

Transact-SQL

1

2

3

4

5

SELECT*FROM#LeftSample

EXCEPT

SELECT*FROM#RightSample;

Results:

1

2

3

4

5

6

7

8

MovieID Movies

-----------------------------------------

3Star Trek

4Iron Man

7ANightmare Before Christmas

9Dune

11Back tothe Future

12Weird Science

As you can see, the EXCEPT operator is returning the data from the left query and not the right. This means that you have to know which query you want the data to be returned from and it’s that query that goes before the EXCEPT operator. If we swap the tables over, we’ll get a different set of results:

EXCEPT - Tables swapped

Transact-SQL

1

2

3

4

5

SELECT*FROM#RightSample

EXCEPT

SELECT*FROM#LeftSample;

Results:

1

2

3

4

5

6

7

8

MovieID Movies

------------------------------------

3Guardians of the Galaxy

4Batman

7Labyrinthe

9ShortCircuit

11Back tothe Future2

12Teen Wolf

Time to play

As you can see both INTERSECT and EXCEPT are easy to use and understand. Now it’s time for you to go away and play with more than two queries i.e: Query1 INTERSECT Query2 EXCEPT Query 3 – give it a go and see what happens (hint you might need another temp table!).