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:
|
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 |
IF OBJECT_ID('tempdb..#LeftSample') IS NOT NULL BEGIN DROP TABLE #LeftSample END; IF OBJECT_ID('tempdb..#RightSample') IS NOT NULL BEGIN DROP TABLE #RightSample END; GO CREATE TABLE #LeftSample ( MovieID INT IDENTITY(1,1) NOT NULL , Movies VARCHAR(100) NOT NULL ); CREATE TABLE #RightSample ( MovieID INT IDENTITY(1,1) NOT NULL , Movies VARCHAR(100) NOT NULL ); GO SET NOCOUNT ON; INSERT INTO #LeftSample VALUES ('Star Wars'); INSERT INTO #LeftSample VALUES ('Lord of the Rings'); INSERT INTO #LeftSample VALUES ('Star Trek'); INSERT INTO #LeftSample VALUES ('Iron Man'); INSERT INTO #LeftSample VALUES ('Alien'); INSERT INTO #LeftSample VALUES ('Indianna Jones'); INSERT INTO #LeftSample VALUES ('A Nightmare Before Christmas'); INSERT INTO #LeftSample VALUES ('Serenity'); INSERT INTO #LeftSample VALUES ('Dune'); INSERT INTO #LeftSample VALUES ('Blade Runner'); INSERT INTO #LeftSample VALUES ('Back to the Future'); INSERT INTO #LeftSample VALUES ('Weird Science'); INSERT INTO #RightSample VALUES ('Star Wars'); INSERT INTO #RightSample VALUES ('Lord of the Rings'); INSERT INTO #RightSample VALUES ('Guardians of the Galaxy'); INSERT INTO #RightSample VALUES ('Batman'); INSERT INTO #RightSample VALUES ('Alien'); INSERT INTO #RightSample VALUES ('Indianna Jones'); INSERT INTO #RightSample VALUES ('Labyrinthe'); INSERT INTO #RightSample VALUES ('Serenity'); INSERT INTO #RightSample VALUES ('Short Circuit'); INSERT INTO #RightSample VALUES ('Blade Runner'); INSERT INTO #RightSample VALUES ('Back to the Future 2'); INSERT INTO #RightSample VALUES ('Teen Wolf'); GO |
Intersect:
The following INTERSECT statement will return the distinct rows from both the left query and the right query.
|
1 2 3 4 5 |
SELECT * FROM #LeftSample INTERSECT SELECT * FROM #RightSample; |
Results:
|
1 2 3 4 5 6 7 8 |
MovieID Movies ----------- ------------------- 1 Star Wars 2 Lord of the Rings 5 Alien 6 Indianna Jones 8 Serenity 10 Blade Runner |
EXCEPT
The EXCEPT operator will returns data from the left query that isn’t in the right query.
|
1 2 3 4 5 |
SELECT * FROM #LeftSample EXCEPT SELECT * FROM #RightSample; |
Results:
|
1 2 3 4 5 6 7 8 |
MovieID Movies ----------- ------------------------------ 3 Star Trek 4 Iron Man 7 A Nightmare Before Christmas 9 Dune 11 Back to the Future 12 Weird 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:
|
1 2 3 4 5 |
SELECT * FROM #RightSample EXCEPT SELECT * FROM #LeftSample; |
Results:
|
1 2 3 4 5 6 7 8 |
MovieID Movies ----------- ------------------------- 3 Guardians of the Galaxy 4 Batman 7 Labyrinthe 9 Short Circuit 11 Back to the Future 2 12 Teen 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!).
Leave a Comment