Articles → SQL SERVER → 'Except' Vs 'Not In' In SQL Server

'Except' Vs 'Not In' In SQL Server

Most of the time, we use "not in" and "except" interchangeably. But they differ in terms of the number of rows returned and performance. Here, I will explain the difference between the two.

Sample Table And Data

Before we discuss the differences, let us create 2 tables that contain only 1 column "num". You can create the sample table with the following code.

 -- Create Table
  CREATE TABLE Table1 (num INT);
  CREATE TABLE Table2 (num INT);

Let’s put some data in the table.

-- Adding data in Table1
INSERT INTO Table1  VALUES (10)
INSERT INTO Table1  VALUES (11)
INSERT INTO Table1  VALUES (12)
INSERT INTO Table1  VALUES (13)
INSERT INTO Table1  VALUES (10)
-- Adding data in Table2
INSERT INTO Table2  VALUES (11)
INSERT INTO Table2  VALUES (12)

Performance-Wise

Let us create the execution plan of both queries and see the performance query-wise. If you see the SQL queries in the preceding section, you could see that there is no index defined on both tables. Let us run the following queries.

-- Except 
SELECT * FROM Table1 
EXCEPT
SELECT * FROM Table2 

-- NOT IN 
SELECT * FROM Table1 
WHERE num NOT IN (SELECT num FROM Table2)

If you check the execution plan, you could see that the performance of "not in" is better than "except". As "except" spent most of the cost in sorting records.

Picture showing the execution plan of the except in sql server

Let’s create a clustered index on both the tables using the following queries.

-- Create Clustered Index
Create Clustered Index idx_num_Table1 On Table1 (num)
Create Clustered Index idx_num_Table2 On Table2 (num)

Now let’s run the preceding "not in" and "except" queries again and see the execution plan.

Picture showing the execution plan of except and not in to compare the performance

As you can see in the preceding figure ‘except’ is performing better because the costly operation i.e., sorting is taken care of by the index.

Difference

‘Except’ returns distinct values whereas ‘not in’ returns duplicate values as well
‘Except’ sort the values in the column if there is no index defined on the column

To validate the preceding 2 points, you can see the result of the select statements.

Picture showing the difference in output of the not in and except in sql server

‘Except’ treats null values as matching. To validate it, add a null value in Table1 using the following insert statements

-- Insert Null value
INSERT INTO Table1  VALUES (Null)

Now run both queries again.

In the preceding image ‘except’ also considers a null value whereas ‘not in’ ignores it.

Posted By -	Karan Gupta

Posted On -	Friday, June 5, 2015

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**