One way to find the rows from one table that don't match another table is to use a sub-query. Here is an example:

--create and populate tables
create table table1(col1 int null)
create table table2(col1 int null)

insert into table1(col1)
select 1
UNION select 2
UNION select 3

insert into table2(col1)
select 1
UNION select 3

--Run this query to find out that the value 2 is missing from table2
select col1 from table1
where col1 not in(select col1 from table2)

NULLs are allowed in the col1 column of table2. If there is a
NULL row, you will get invalid results.

--Insert NULL
insert into table2(col1)
select NULL

--This returns no results, even though the value 2 is still missing
select col1 from table1
where col1 not in(select col1 from table2)

In my opinion, this is a bit tricky to understand. Recall that anything compared to NULL returns unknown. When the value 2 from table1 is compared to the NULL in table2, unknown is returned. That means that it is impossible to know if 2 has a match in table2.

One way around this is to make sure that no NULL values are in the sub-query:

select col1 from table1
where col1 not in(select col1 from table2 where col1 is not null)

My favorite way to write this query is to use a left join:

select t1.col1
from table1 t1 left join table2 t2
on t1.col1 = t2.col1
where t2.col1 is null

Find all rows in table1 even if there isn't a match in table2. Then filter to return only the rows that don't match.

--********************************
Learn more T-SQL tips by attending the End to End T-SQL course http://www.endtoendtraining.com/public/classes/coursedetails.aspx?courseid=17
 to be held in Orlando March 21 and April 1. This course is geared for T-SQL beginners.

http://www.sqlservercentral.com/NewsletterArchive/2008/01/23/384787