Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : How to fetch records in one table that are not present in another table?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 291
    Comment on it

    In this article we will see how to fetch records in one table that are not present in another table.

    First let us create two tables which will be used for illustration purpose:

    CREATE TABLE Table1
    (
        ID INT
    )
    GO
    
    CREATE TABLE Table2
    (
        ID INT
    )
    GO
    

    Now let us say we want to find records in Table1 that do not exist in Table2.Below are the three possible solutions.

    1) Using NOT IN

      SELECT ID FROM Table1 
      WHERE ID NOT IN 
      ( SELECT ID FROM Table2  WHERE Table1.ID = Table2.ID )
    

    2) Using NOT EXISTS

    SELECT ID FROM Table1 
    WHERE NOT EXISTS 
    ( SELECT ID FROM Table2  WHERE Table1 .ID = Table2.ID )
    

    3) Using LEFT JOIN

    SELECT Table1.ID FROM Table1 
    LEFT JOIN Table2  ON Table1.ID = Table2.ID 
    WHERE Table2.ID IS NULL
    

    Out of the above three approaches NOT EXISTS and NOT IN solutions are the better way to search for missing records assuming both columns used are NOT NULL. Both approach produce the same efficient plans. LEFT JOIN is less efficient because in this approach the already matched values in the right table are not skipped.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: