How to use EXCEPT Operator

Leave a comment (1) Go to comments

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

How to use EXCEPT Operator sql server EXCEPT operator v/s NOT IN operator sql server EXCEPT Operator Minus SQL Server Minus Oracle SQL Server Minus Keyword in SQL Server Except sql server EXCEPT operator vs. NOT IN EXCEPT operator vs NOT IN operator sql server EXCEPT operator v/s NOT IN operator Except Operator sql server EXCEPT operator NOT IN operator which is better in sql server EXCEPT operator NOT IN operator sql server Except Operator EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle Any way to select all fields except

Execution plan of the above not in clause query, Solution 3

How to use EXCEPT Operator sql server EXCEPT operator v/s NOT IN operator sql server EXCEPT Operator Minus SQL Server Minus Oracle SQL Server Minus Keyword in SQL Server Except sql server EXCEPT operator vs. NOT IN EXCEPT operator vs NOT IN operator sql server EXCEPT operator v/s NOT IN operator Except Operator sql server EXCEPT operator NOT IN operator which is better in sql server EXCEPT operator NOT IN operator sql server Except Operator EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle Any way to select all fields except

Lets look at execution plan of Solution 1, which uses  EXCEPT clause

How to use EXCEPT Operator sql server EXCEPT operator v/s NOT IN operator sql server EXCEPT Operator Minus SQL Server Minus Oracle SQL Server Minus Keyword in SQL Server Except sql server EXCEPT operator vs. NOT IN EXCEPT operator vs NOT IN operator sql server EXCEPT operator v/s NOT IN operator Except Operator sql server EXCEPT operator NOT IN operator which is better in sql server EXCEPT operator NOT IN operator sql server Except Operator EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle Any way to select all fields except

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

EOF - How to use EXCEPT Operator, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

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

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.