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

'Except' Vs 'Not In' In SQL Server






Sample Table And Data




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




-- 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




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

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




Picture showing the execution plan of the except in sql server




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




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





Difference


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


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


  4. ‘Except’ treats null values as matching. To validate it, add a null value in Table1 using the following insert statements
  5. -- Insert Null value
    INSERT INTO Table1  VALUES (Null)




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





Posted By  -  Karan Gupta
 
Posted On  -  Friday, June 5, 2015

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250