Microsoft introduced the EXCEPT operator in SQL Server 2005, which returns all of the distinct rows from the left side of the EXCEPT operator. It also removes all of the rows from the result set that match the rows that are on the right side of the EXCEPT operator. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.
EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle
Let me give you real scenario, which worked today to meet my business requirement. On daily basis, I send daily newsletters to my subscribers and now there are some customer who initially registered to get newsletters via Email and now there unsubscribed the request, So I need to send newsletters to all customers except the customer who unsubscribed.
Following code, illustrate the two tables, which I have,
|
/*First Table, which has information of all customer, who subscribed for newsletters*/ use tempdb go create table News_Letter_Customer(id int, Fname varchar(50),email varchar(100)) go Insert into News_Letter_Customer values (1,‘ananth_bba’,‘ananth_bba@yahoo.co.in’) Insert into News_Letter_Customer values (2,‘jayasriravi’,‘jayasriravi@rediff.com’) Insert into News_Letter_Customer values (3,‘velusms’,‘velusms@yahoo.com’) Insert into News_Letter_Customer values (4,‘rjagannath_in’,‘rjagannath_in@yahoo.com’) Insert into News_Letter_Customer values (5,‘abcd’,‘abcd@gmail.com’) Insert into News_Letter_Customer values (6,‘kevinm488′,‘kevinm488@yahoo.co.in’) Insert into News_Letter_Customer values (7,‘kar_trick’,‘kar_trick@yahoo.co.in’) Insert into News_Letter_Customer values (8,‘ganapathyvijai’,‘ganapathyvijai@gmail.com’) Insert into News_Letter_Customer values (9,‘isherrif’,‘isherrif@yahoo.co.in’) Insert into News_Letter_Customer values (10,‘john’,‘johnsonjeeva@gmail.com’) Insert into News_Letter_Customer values (11,‘jaya’,‘jayasundaram_87@yahoo.co.in’) Insert into News_Letter_Customer values (12,‘krpradeep1984′,‘krpradeep1984@hotmail.com’) Insert into News_Letter_Customer values (13,‘mani_subra’,‘mani_subra@yahoo.co.in’) Insert into News_Letter_Customer values (14,‘its_karthik03′,‘its_karthik03@yahoo.com’) Insert into News_Letter_Customer values (15,‘chandmadevi’,‘chandmadevi@yahoo.com’) Insert into News_Letter_Customer values (16,‘jaykris_2006′,‘jaykris_2006@yahoo.co.in’) Insert into News_Letter_Customer values (17,‘goyal735′,‘goyal735@gmail.com’) Insert into News_Letter_Customer values (18,‘hotmailkrish’,‘hotmailkrish@yahoo.com’) Insert into News_Letter_Customer values (19,‘geethabcs’,‘geethabcs@yahoo.com’) Insert into News_Letter_Customer values (20,‘gopal’,‘chithra_gopal@rediffmail.com’) Insert into News_Letter_Customer values (21,‘bala’,‘bala_322muruga@yahoo.co.in’) Insert into News_Letter_Customer values (22,‘jahir111′,‘jahir111@yahoo.com’) Insert into News_Letter_Customer values (23,‘web_bipin’,‘web_bipin@yahoo.com’) Insert into News_Letter_Customer values (24,‘aroune.k’,‘aroune.k@gmail.com’) Insert into News_Letter_Customer values (25,‘mahalakshmi13′,‘mahalakshmi13@yahoo.com’) go /*Second Table, which has information of all customer, who UN- subscribed for newsletters*/ create table Un_Registered_Customers(id int, Fname varchar(50),email varchar(100)) Insert into Un_Registered_Customers values (8,‘vijai’,‘ganapathyvijai@gmail.com’) Insert into Un_Registered_Customers values (17,‘goyal735′,‘goyal735@gmail.com’) Insert into Un_Registered_Customers values (19,‘geetha’,‘geethabcs@yahoo.com’) Insert into Un_Registered_Customers values (21,‘bala’,‘bala_322muruga@yahoo.co.in’) Insert into Un_Registered_Customers values (27,‘baalajee_s’,‘baalajee_s@yahoo.co.in’) Insert into Un_Registered_Customers values (34,‘lokesh1982s’,‘lokesh1982s@yahoo.com’) Insert into Un_Registered_Customers values (41,‘shekargh’,‘shekargh@rediffmail.com’) Insert into Un_Registered_Customers values (48,‘fergus’,‘milton.fergus@rediffmail.com’) Insert into Un_Registered_Customers values (55,‘muthu’,‘ahdus_muthu@rediffmail.com’)
|
Now I wanted to get a list of record to send a daily newsletters to ( All Customers in table News_Letter_Customer - All Customers in table Un_Registered_Customers )
So, I can used any of the below mentioned query but Solution 1, uses EXCEPT clause, which is optimized way of getting the results
|
/* Query to meet the business Requirement — Solution 1 */ SELECT * FROM News_Letter_Customer EXCEPT SELECT * FROM Un_Registered_Customers /* Solution 2, to meet the business Requirement */ SELECT * FROM News_Letter_Customer where email not in ( SELECT email FROM Un_Registered_Customers )
|
EXCEPT clause, which is optimized way of getting the results bceuase
- In the above example only one value is compared, but if you want to compare all three columns values, then the not in clause will have additional sub query I/O
If wanted to compare all three columns value then I need to write the following TSQL Code, which is very I/O expensive
|
/* Solution 3, to meet the business Requirement */ SELECT * FROM News_Letter_Customer where email not in ( SELECT email FROM Un_Registered_Customers ) and ID not in ( SELECT ID FROM Un_Registered_Customers ) and Fname not in ( SELECT Fname FROM Un_Registered_Customers )
|
Lets look at execution plan of the above not in clause query, Solution 3
Execution plan of the above not in clause query, Solution 3
Lets look at execution plan of Solution 1, which uses EXCEPT clause
So now onwards, I am going to use the following query to meet my requirement.
|
/* Query to meet the business Requirement — Solution 1 */ SELECT * FROM News_Letter_Customer EXCEPT SELECT * FROM Un_Registered_Customers
|
Tags : Any way to select all fields except,EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle,Except Operator, Except sql server, Except Operator sql server,Minus Keyword in SQL Server,Minus SQL Server, Minus Oracle SQL Server,EXCEPT Operator sql server, sql server EXCEPT Operator,EXCEPT operator vs. NOT IN,EXCEPT operator v/s NOT IN operator,sql server EXCEPT operator v/s NOT IN operator, EXCEPT operator vs NOT IN operator sql server, EXCEPT operator NOT IN operator sql server,EXCEPT operator NOT IN operator which is better in sql serverr
Have you compared EXCEPT and NOT EXISTS?
If you compare the execution plans of the following two SQL queries, you will see that the NOT EXISTS query is cheaper.
SELECT *
FROM News_Letter_Customer AS nlc
WHERE NOT EXISTS ( SELECT email
FROM Un_Registered_Customers AS urc
WHERE urc.email = nlc.email
AND urc.id = nlc.id
AND urc.Fname = nlc.Fname );
SELECT * FROM News_Letter_Customer
EXCEPT
SELECT * FROM Un_Registered_Customers;
However, the EXCEPT clause is a lot easier to write and understand than the NOT EXISTS.
As always in SQL Server, there is more than one way of doing the task and it depends on the circumstances as to which one you use.