SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

The EXCEPT operator returns all of the distinct rows from the query to the left of the EXCEPT operator when there is no matching rows in the right query. The EXCEPT operator is equivalent of the Left Anti Semi Join. EXCEPT operator works the same way NOT IN. EXCEPTS returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

Example of EXCEPT operator is displayed along Example of NOT IN. If you run both of the Query and compare the Execution plan it is exactly same. EXCEPT and NOT IN does same functions and have same execution plan but EXCEPT has much simpler syntax. The row-by-row comparison provided by EXCEPT, combined with the number of rows being returned remaining consistent, provides compelling evidence that re-factored query is correct. EXCEPT works with * as well as alias.

Example: (Both of the scripts returns same number of rows)
----SQL SERVER 2005 Method
USE AdventureWorks;
GO

SELECT ProductID
FROM Production.Product
EXCEPT
SELECT
ProductID
FROM Production.WorkOrder ;
GO

----SQL SERVER 2000 Method which works&nbspIN SQL SERVER 2005
USE AdventureWorks;
GO

SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO

Following the execution plan which indicates EXCEPT and NOT IN uses same execution plan. Click on below image to see larger image.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

62 thoughts on “SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

  1. Pinal,

    I would suggest against a NOT IN clause as any time a NULL comes back in the NOT IN set, the query will bring back an empty set.

    NOT EXISTS seems like a better option, as NULLs do not cause the same problems and runs with the same performance.

    SELECT ProductID
    FROM Production.Product P
    WHERE NOT EXISTS (
    SELECT ProductID
    FROM Production.WorkOrder W
    WHERE P.ProductID = W.ProductID);

    My two cents.

    Reeves

  2. There are centain shortcomings while using EXCEPT with TOP expression

    When you want to retrieve TOP 1 row from the query, you may get 0 rows, even though there might be many qualifed rows.

    This will happen when the first row in the left query is present in the right query.

    Example

    SELECT TOP (1) ProductID
    FROM Production.Product
    EXCEPT
    SELECT ProductID
    FROM Production.WorkOrder ;

    Again, when you try to retieve TOP 100, you may get less than 100 rows.

    This will not happen when you use NOT IN.

    Mahender Pabba

  3. Pinal,

    Even if you have qualified rows still you wont get desired result

    Example

    create table t1 (Product_Id tinyint)
    create table t2 (Product_Id tinyint)

    insert into t1 values (1)
    insert into t1 values (2)
    insert into t1 values (3)
    insert into t1 values (4)
    insert into t2 values (1)

    select * from t1
    except
    select * from t2

    Result (3 rows)
    2
    3
    4

    select top 1 * from t1
    except
    select * from t2

    Result (0 rows)

    select top 2 * from t1
    except
    select * from t2

    Result (1 rows)
    2

    Regards,
    Mahender Pabba

  4. Hi Mahender,

    Try the below one to get the top1 row

    select top 1 * from
    (
    select * from t1
    except
    select * from t2) as t

    Since in your earlier query top CLAUSE is applied on first select statement, so the resultant is what used for except operation.

    Regards,
    Thivya

  5. Hi,

    How can I use NOT IN clause if I have 2 different conditions to check on my query? Here, I can have valid repeating values for both EmpId and GlobalRoldId as in the example below. Right now, I am using an EXCEPT clause though its working fine right now.

    For e.g:
    SELECT EmpId, GlobalRoleId
    FROM dbo.tbl_PTS_EmpEntityRoleMapping
    EXCEPT
    SELECT EmpId, GlobalRoleId
    FROM EmpEntityRoles

    Thanks in advance.

  6. You can do it with Not exists in this way

    SELECT EmpId, GlobalRoleId
    FROM dbo.tbl_PTS_EmpEntityRoleMapping a
    where Not Exists
    ( SELECT * FROM EmpEntityRoles b
    where b.EmpId = a.EmpId
    and b.GlobalRoleId = a.GlobalRoleId
    )

    Pötyös

  7. Does anybody know how to compare columns of two records, and replace values in SQL server 2003.

    For example, if I have:

    Class ID TeacherID Start Date

    100 105 9/5/05

    100 105 12/5/05

    Lets say I have several record sets like this. How do I compare values of similar record sets to get the earliest start date? Thanx!

  8. I get the results I want when I use Except but it returns no rows when I use NOT IN. Why is this?

    SQL SERVER 2005

    SELECT RIGHT(Username,6) From Table1
    Where RIGHT(Username, 6) NOT IN (Select RIGHT(Username, 6) from Table2)

    returns 0 rows

    SELECT RIGHT(Username, 6) From Table1
    Except (Select RIGHT(Username, 6) from Table2)

    returns 3 rows like I expect.

    Is it due to the RIGHT modification and its evaluation?

  9. Hi,

    The following is a query which works with not in. How do I get the same output on sql server?

    select col1
    from table1
    where (col2, col3) not in
    (select colA, colB
    from table 2)
    [Here, colA ~ col2 and colB ~ col3, so the data types are same]

    Any help will be appreciated!

    Thanks,
    Kalpa

  10. Hi i have two tables

    I have to compare this 2 tables…,

    Now which dates are not in table1 that date and that ename have to come in select statement

    Table1:

    TSID TSdate EName
    100 7-21-2008 Name1
    101 7-21-2008 Name1
    102 7-22-2008 Name1
    103 7-26-2008 Name1
    104 7-23-2008 Name2
    105 7-25-2008 Name2

    Table2:

    Nodates
    7-21-2008
    7-22-2008
    7-23-2008
    7-24-2008
    7-25-2008
    7-26-2008

    output:
    Date Name
    7-23-2008 Name1
    7-24-2008 Name1
    7-25-2008 Name1
    7-21-2008 Name2
    7-22-2008 Name2
    7-24-2008 Name2
    7-26-2008 Name2

    how to do that

  11. @compare 2 tables

    I am sure there must be other ways to do this, I used a cursor and while loop,

    This can also be done with one cursor with out using a while loop, since the third column in first table contains duplicate rows, the cursor will be executed many times ( how many duplicate rows you have in column 3 in first table, that many times) I thought that would unneccarily consume performance so I chose cursor and while loop to increase performance of the query.

    In short, this is also performence consuming, but I could not think of anything else,.. this is atleast a better one in my view..

    Here starts your code….

    CREATE TABLE TABLE_1 (TSID INT, TSDATE VARCHAR(10), ENAME VARCHAR(10))

    INSERT INTO TABLE_1 (TSID , TSDATE, ENAME )
    SELECT 100, ’7-21-2008′ ,’NAME1′UNION ALL
    SELECT 101, ’7-21-2008′, ‘NAME1′UNION ALL
    SELECT 102, ’7-22-2008′, ‘NAME1′UNION ALL
    SELECT 103, ’7-26-2008′, ‘NAME1′UNION ALL
    SELECT 104, ’7-23-2008′, ‘NAME2′UNION ALL
    SELECT 105,’7-25-2008′, ‘NAME2′

    CREATE TABLE TABLE_2 (NODATES VARCHAR(10))

    INSERT INTO TABLE_2 (NODATES)
    SELECT ’7-21-2008′UNION ALL
    SELECT ’7-22-2008′ UNION ALL
    SELECT ’7-23-2008′UNION ALL
    SELECT ’7-24-2008′UNION ALL
    SELECT ’7-25-2008′UNION ALL
    SELECT ’7-26-2008′

    We created two tables, and inserted the sample data given by you,

    I am creating two temporary tables, and then I dropped those two temporary tables at the end of the code.

    SET NOCOUNT ON
    DECLARE @ENAME VARCHAR(10)
    DECLARE @DATE VARCHAR(10)
    DECLARE @VAR INT
    CREATE TABLE #RESULT ( DATE VARCHAR(10), NAME VARCHAR(10))
    CREATE TABLE #TEMP1 (ID INT IDENTITY, ENAME VARCHAR(10))
    INSERT INTO #TEMP1(ENAME) SELECT DISTINCT ENAME FROM TABLE_1
    DECLARE @CUR CURSOR
    SET @CUR = CURSOR FOR
    SELECT DISTINCT NODATES FROM TABLE_2
    OPEN @CUR
    FETCH NEXT
    FROM @CUR INTO @DATE
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @VAR = 1
    WHILE @VAR < = (SELECT COUNT(1) FROM #TEMP1)
    BEGIN
    SELECT @ENAME = ENAME FROM #TEMP1 WHERE ID = @VAR
    IF EXISTS ( SELECT TSDATE FROM TABLE_1 WHERE TSDATE = @DATE)
    BEGIN
    IF EXISTS (SELECT ENAME FROM TABLE_1 WHERE TSDATE = @DATE AND ENAME @ENAME )

    BEGIN
    INSERT INTO #RESULT (DATE , NAME ) SELECT @DATE ,@ENAME
    END
    END
    ELSE INSERT INTO #RESULT (DATE , NAME) SELECT @DATE, @ENAME
    SET @VAR = @VAR +1
    END
    FETCH NEXT
    FROM @CUR INTO @DATE
    END
    CLOSE @CUR
    DEALLOCATE @CUR
    SELECT * FROM #RESULT ORDER BY NAME , DATE
    DROP TABLE #TEMP1
    DROP TABLE #RESULT
    GO

    Result :

    7-23-2008 Name1
    7-24-2008 Name1
    7-25-2008 Name1
    7-21-2008 Name2
    7-22-2008 Name2
    7-24-2008 Name2
    7-26-2008 Name2

    Hope this helps,
    Thanks,
    Imran.

  12. Hello Sir,
    I used topic
    “17 Responses to “SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN””of( Reeves on July 19, 2007 at 9:57 am
    )

    It helped me lot in my problem.Thanks lot.

    Thank you very much.
    See you again.

    Nandkishor

  13. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  14. Hi all,
    I want to compare two tables from two databases,where one is original database and another one is local database table.

    Now,how do i compare both the tables,such that.when compared with original database if any record or column is updated in the local database,it should return me updated records.

    Using Java and SQL ,how can i do this.Please reply as earle as possible.

    Thanks in Advance!!!

  15. Hi Sudha
    you can do this with a timestamp column, everytime record is created or update you need to update the timestamp and than to find updated rows you just need to check the timestamp in orginal and local db.

    You can also use red-gate to compare data and sql it’s very good DB tool.

  16. Hey pinal i read your blog a lot but never commented or asked question . This is the first time .

    What i came across is this
    i have a table with around 90000 rows .
    45000 where source_flag=1 and 45000 where target_flag=1

    i was using a not in query like this

    SELECT value FROM die_domain_log WHERE source_flag =1 AND log_id=103
    NOT IN ( SELECT i.value FROM die_domain_log i WHERE i.target_flag =1 AND i.log_id=103 )

    When executed it didnt returned any results for like 15-20 mins i stoped it .

    Then i changed it to except it took less than a sec to return the result .

    SELECT value FROM die_domain_log WHERE source_flag =1 AND log_id=103
    EXCEPT
    SELECT i.value FROM die_domain_log i WHERE i.target_flag =1 AND i.log_id=103

    why is this happening ?

  17. @Shrikanth Patil.

    Your first select statement is not valid. I am not sure if it could ever execute, because you are not specific about what to be search for NOT IN.

    Anyways below is a working example, consider this example and let us know if this is what you were expecting.

    Example Script :

    create table #die_domain_log ( Value int identity , source_flag int , target_flag int, log_id int )

    insert into #die_domain_log values ( 1, NULL , 103)
    insert into #die_domain_log values ( 1, NULL , 103)
    insert into #die_domain_log values ( 1, NULL , 103)
    insert into #die_domain_log values ( 1, NULL , 103)
    insert into #die_domain_log values ( 1, NULL , 103)

    insert into #die_domain_log values ( NULL , 1, 103)
    insert into #die_domain_log values ( NULL , 1, 103)
    insert into #die_domain_log values ( NULL , 1, 103)
    insert into #die_domain_log values ( NULL , 1, 103)
    insert into #die_domain_log values ( NULL , 1, 103)

    Select * from #die_domain_log

    – Here you need to be specific what to search for NOT IN, I have include Value as a search criteria for NOT IN.

    SELECT value
    FROM #die_domain_log
    WHERE source_flag =1
    AND log_id=103
    AND value
    NOT IN
    (
    SELECT i.value
    FROM #die_domain_log i
    WHERE i.target_flag =1
    AND i.log_id=103
    )

    – OR ( below is same as you posted)

    SELECT value FROM #die_domain_log WHERE source_flag =1 AND log_id=103
    EXCEPT
    SELECT i.value FROM #die_domain_log i WHERE i.target_flag =1 AND i.log_id=103

    ~ IM.

  18. Sorry i forgot to add where value IN in the above post . In actual ‘m using the same query u have return it works for small number of rows also . but when i have 90000 rows as i said above it doesnt work the way it should .

  19. Can any one help me with such a query below.

    SELECT DISTINCT Building WHERE Building IS NOT NULL
    AND Building NOT LIKE ‘%School%’
    AND Building NOT LIKE ‘%Church%’
    AND Building NOT LIKE ‘%Rectory%’
    AND Building NOT LIKE ‘%Public House%’
    AND Building NOT LIKE ‘%Retail Park%’
    AND Building NOT LIKE ‘%Railway Station%’
    AND Building NOT LIKE ‘%Post Office%’
    AND Building NOT LIKE ‘%Court%’
    AND Building NOT LIKE ‘%Floor%’
    AND Building NOT LIKE ‘%Flat%’
    AND Building NOT LIKE ‘%Former%’
    AND Building NOT LIKE ‘%Lodge%’

    basically as per my requirement, the list in the not like might increase daily, I dont find the query good with the whole list as above. Is there a way I can avoid it.

    Please suggest.

  20. @Sucharitha

    Ideally there should be a type field instead of searching a name for the type.

    If there is a list that will be managed daily, perhaps another TABLE would be best. Then the query could use a join and not have to change.

  21. Hello Brian,

    Thank you for the suggestion which was obviously in my mind. I am new into the team and company, and the application has been built and stabilised. I need to study it better before i make any changes to the DB.
    But at this time, I have been asked to get a quick report where I need to filter on something like this. Is there any other quick solution running in your mind?

  22. @Sucharitha

    I don’t know of any. (Though i am not an expert.)

    It seems that every field would need to be scanned, resulting in a full table scan. Indexing won’t even help because it would be indexing off the description and not the part that you want.

  23. Is there script in SQL SERVER to support multi fields like Oracle in “WHERE (col1,col2) NOT IN(…)” ?

    Here is the script I want to run bot not support in SQL Server. It works in Oracle. [INSTITUTION],[CODE] ,[EFF_DATE] are composite primary key.

    SELECT *
    from DDEF_CRS_Stage
    where ([INSTITUTION]
    ,[CODE]
    ,[EFF_DATE])
    NOT IN
    (
    select [INSTITUTION]
    ,[CODE]
    ,[EFF_DATE] from DDEF_CRS_Stage
    group by [INSTITUTION]
    ,[CODE]
    ,[EFF_DATE]
    Having COUNT(*) > 1
    )

    Thank you.

    • @david

      Like Pinal said, make it an EXISTS. I’d like to add, that the NOT IN (or NOT EXISTS) can be IN (or EXISTS) as it can be checked to = 1 instead of being NOT > 1.

      However, the best of all is to use neither. Since the query limits the results to one record per group (as any groups with multiple records are excluded) using aggregates will have no effect on the results:

      SELECT
      [INSTITUTION],
      [CODE],
      [EFF_DATE],
      MAX(Col1),
      MAX(Col2),
      MAX(Col3)
      FROM
      DDEF_CRS_Stage
      GROUP BY
      [INSTITUTION],
      [CODE]
      [EFF_DATE]
      HAVING
      COUNT(*) = 1;

  24. Hello David,

    Multiple columns in single where clause is not supported in SQL Server. You have to write a saperate NOT IN clause fora each column.
    Another better way to perform the same thing is using NOT EXISTS clause where you would not have to write saperate subquery for each column.

    Kind Regards,
    Pinal Dave

  25. Hi Sucharitha,

    Eevery time your are checking with only one column, “building”. So create a separate table with a single column. ex tblBuildingList(strBuilding VARCHAR(100).

    INSERT INTO tblBuildingList(strBuilding) VALUES(%School%)
    INSERT INTO tblBuildingList(strBuilding) VALUES(%Church%)
    …….

    select Building from yourTableName tbl
    INNER JOIN tblBuildingList bld ON
    tbl.Building NOT LIKE bld.strBuilding

    I think it ll solve your problem, bcz no need to change the query when ever adding new list. just insert the list value into the tblBuildingList.

    Thanks

  26. Hi Brian and Pinal

    Your guys are rocks!

    I like the solution such as
    SELECT
    [INSTITUTION],
    [CODE],
    [EFF_DATE],
    MAX(Col1), –solve the problem with column not group by
    MAX(Col2),
    MAX(Col3)
    FROM
    DDEF_CRS_Stage
    GROUP BY
    [INSTITUTION],
    [CODE]
    [EFF_DATE]
    HAVING
    COUNT(*) = 1;

    The following code, I have to use table variables to solve the same problem in SQL Server.

    declare @tbl table([INSTITUTION] varchar(4)
    ,[CODE] varchar(20)
    ,[EFF_DATE] datetime);

    Insert into @tbl
    select [INSTITUTION]
    ,[CODE]
    ,[EFF_DATE] from DDEF_CRS_Stage
    group by [INSTITUTION]
    ,[CODE]
    ,[EFF_DATE]
    Having COUNT(*) > 1;

    select * from DDEF_CRS_Stage
    EXCEPT
    (
    select * from DDEF_CRS_Stage
    where [INSTITUTION] IN
    (select [INSTITUTION] from @tbl)
    AND [CODE] IN
    (select [Code] from @tbl)
    AND [EFF_DATE] IN
    (select [EFF_DATE] from @tbl)
    )

    Have a fun with your guys.

    David

  27. –following except is not working

    select ad.code ,ad.description, count (di.cod_acct_no)as “No of Cards” from account_master d, accountinfo di, branch ad
    where d.p_acct_no=di.cod_acct_no and ad.branchid=di.branchid
    and di.dat_acct_open between ’2010-02-27 00:00:00′
    and ’2010-03-11 00:00:00′
    and d.iss_date between ’2010-02-27 00:00:00′
    and ’2010-03-11 23:59:00′
    and accounttypeid in (1,2,3,55)
    group by ad.code, ad.description
    order by ad.code, ad.description
    EXCEPT
    select ad.code ,ad.description, count (di.cod_acct_no)as “No of Cards” from account_master d, accountinfo di, branch ad
    where d.p_acct_no=di.cod_acct_no and ad.branchid=di.branchid
    and di.dat_acct_open between ’2010-02-20 00:00:00′
    and ’2010-03-11 00:00:00′
    and d.iss_date between ’2010-02-20 00:00:00′
    and ’2010-03-11 23:59:00′
    and accounttypeid in (1,2,3,55)
    group by ad.code, ad.description
    order by ad.code, ad.description

  28. Hi;
    any opinion about why the first query returns data but the second and third query not return data?

    select 1 where 1 in (null, 1, 2, 3);
    select 1 where 10 not in (null, 1, 2, 3);
    select 1 where not 10 in (null, 1, 2, 3);

    • Hi,

      This is due the Ansi_nulls property ON.
      In the first statement
      select 1 where 1 in (null, 1, 2, 3);
      The compiler take the value 1 and compares whether it exists in the “in ” list(null,1,2,3) and outputs ’1′.

      In 2nd stmt

      select 1 where 10 not in (null, 1, 2, 3); the compiler takes the value 10 and tries to compare with null. sinc ethe Ansi_nulls is set On it will not equate to null and hence returns no result set.

      In 3rd statement

      Similar to the 2 nd statement.
      When you turn off the set Ansi_nulls as ‘OFF’ all the 3 select staement will return 1 as value.

  29. Hi all,

    I want to compare two tables from one databases,
    how do i compare both the tables,such that.when compared with table’s columns if any records of column is updated in the either of two table’ columns,it should return me updated records.
    (hints Table1 is old and Table2 to is new updated one and columns are same name as well as data type)

    please help me.

  30. Hello Sir,

    ALTER PROCEDURE [dbo].[Select_Data_zonetext]
    @ZoneCode varchar(200),
    @id bigint

    AS
    BEGIN
    SET NOCOUNT ON;

    select * from tb_xxx where ZoneCode IN(@ZoneCode) and id=@id
    END

    the above sample stored procedure not shows any error but not retrieve required information.. please help me about this . thanks in advance

  31. Thanks for this – I’ve spent ages working out why my NOT IN returns inconsistent results – there were NULLs in my list!

  32. I have a problem , i need to put multiple conditions added with OR operator inside a conditional statement like we do in the common programming language like

    if( a==”2″ || b==”3″)
    {

    }
    else
    {

    }

    i want the same in T-SQL , can you help me !!

  33. hello all,
    the NOT IN is not working for me in the below script, but does work when i use EXCEPT. can you tell me why is this happening?
    below are my script:

    – this one returns an empty dataset
    SELECT DISTINCT dbo.tblGrant.GrantID FROM dbo.tblGrant WHERE dbo.tblGrant.GrantID NOT IN
    (SELECT distinct dbo.tblBeneficiaryServices.GrantID FROM dbo.tblBeneficiaryServices ) AND dbo.tblGrant.GrantID NOT IN
    (SELECT DISTINCT dbo.tblBeneficiaryServiceLog.GrantID FROM dbo.tblBeneficiaryServiceLog )

    – this one returns over 900 records
    SELECT DISTINCT dbo.tblGrant.GrantID FROM dbo.tblGrant EXCEPT
    (SELECT distinct dbo.tblBeneficiaryServices.GrantID FROM dbo.tblBeneficiaryServices union
    SELECT DISTINCT dbo.tblBeneficiaryServiceLog.GrantID FROM dbo.tblBeneficiaryServiceLog )

  34. I find in some cases a comparative join offers more flexibility and may perform better as well. A trivial example would read something like

    select col1, col2, col3
    from table1 t1
    left outer join table2 t2 on t2.col1 = t1.col1
    where t2.col1 is null

    This assumes a grain match between the two related columns. To acount for grain mismatch where multiple rows exist in t2 for each row in t1 this can be tuned as

    select col1, col2, col3
    from table1 t1
    left outer join (select distinct col1 from table2) t2 on t2.col1 = t1.col1
    where t2.col1 is null

    What I like about this approach is that the object we check against can include any number of checks and conditions within the inline view and the comparison list is whatever you want ti to be.

  35. Hi , i am having one table in which i am having unique audit history id . i want to compare both the rows and get only those columns whose values are different and leave the remaining columns. is it possible?????

  36. i want to know how the except operator perform in sql….. plz clear my confusion bcoz i already read about this by book or in net but my cinfusion is still same….

  37. Pingback: SQL SERVER – Weekly Series – Memory Lane – #030 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s