Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE #NewCustomers (ID INT);
- CREATE TABLE #ExistingCustomers (ID INT);
- INSERT INTO #NewCustomers
- ( ID )
- VALUES
- (8), (9), (10), (1), (3), (8);
- INSERT INTO #ExistingCustomers
- ( ID )
- VALUES
- ( 1) , (2), (3), (4), (5);
- -- EXCEPT filters for DISTINCT values
- SELECT * FROM #NewCustomers AS nc
- EXCEPT
- SELECT * FROM #ExistingCustomers AS ec
- -- NOT IN returns all values without filtering
- SELECT * FROM #NewCustomers AS nc
- WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)
- INSERT INTO #ExistingCustomers
- ( ID )
- VALUES
- ( NULL );
- -- With NULL values in the right-hand table, EXCEPT still returns the same results as above
- SELECT * FROM #NewCustomers AS nc
- EXCEPT
- SELECT * FROM #ExistingCustomers AS ec
- -- NOT IN now returns no results
- SELECT * FROM #NewCustomers AS nc
- WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)
- DROP TABLE #NewCustomers;
- DROP TABLE #ExistingCustomers;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement