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

Click to Enlarge



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

Click to Enlarge




Difference








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

Click to Enlarge



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




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

Click to Enlarge




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

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250