MS SQL Server 'not in' syntax


  • I am trying to run the following query in MS SQL Server 2005:


     select count(*) as newevalscount
         from table2
         where cat_key not in(select cat_key from table1)

    MS SQL Server says the query was successful and brought back a count of 0.  However, there ARE several thousand records in table2 that are not in table1. 

    How else can I write this statement to get the results I want.



    Tuesday, June 21, 2011 9:15 PM


  • Try:

    select count(*) as newevalscount
      from table2 T2 
      where not EXISTS (select 1 from table1 T1 where T1.cat_key = T2.cat_key)

    To learn why your version of the query didn't produce the expected result I recommend to check this blog post

    Why you should never use IN/NOT IN in SQL
    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    Tuesday, June 21, 2011 9:23 PM