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 are 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 do not also return 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 the same. EXCEPT and NOT IN does same functions and have the 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 aliases.

Example: (Both of the scripts returns the 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.

SQL SERVER - 2005 Comparison EXCEPT operator vs. NOT IN SQLSERVER_EXCEPT_ExecPlan_small

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Top 15 free SQL Injection Scanners – Link to Security Hacks
Next Post
SQLAuthority News – T-Shirts in Action

Related Posts

63 Comments. Leave new

  • What should i do if i want to return only the columns that do not match for 2 tables ?

    Any help is appreciated.

    Reply
  • 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.

    Reply
  • 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

    Reply
    • The select statement should be

      select * from tb_xxx where ‘,’+@ZoneCode+’,’ like ‘%,’+cast(ZoneCode as varchar(10))+’,%’ and id=@id

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

    Reply
  • Nice share

    Reply
  • 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 !!

    Reply
    • You can use IF-ELSE statement in T-sql too. Refer this post
      Also post some sample data with eected result

      Reply
  • 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 )

    Reply
  • 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.

    Reply
  • thank you sir

    Reply
  • 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?????

    Reply
  • 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….

    Reply
    • select * from table1
      except
      select * from table2

      The result is all the rows from table1 which are not available in table2

      Reply
  • Like the same can we have other operator for IN, which both behave the same functionality

    Reply
  • in case of EXCEPT both the table should have equal no of record , if not You can use NOT IN

    Reply
  • if both table have same no of record in that case you can use EXCEPT other wise use NOT IN

    Reply
  • Hi Guys,

    What’s the cost to use EXCEPT statement in queries that return above of 1,000,000 of rows?

    Grettings
    -Luis

    Reply
  • We can not generalize and say Except is same as NOT IN. Number of data points your are joining/comparing matters.

    Reply

Leave a Reply

Menu