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)





Click to Enlarge



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





Click to Enlarge




Difference









Click to Enlarge



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





Click to Enlarge




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

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250