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.