SQL Server - NOT IN

Question

I need to build a query that will show me records that are in Table 1, but that are not in Table 2, based on the make-model-serial number combination.

I know for fact that there are 4 records that differ, but my query always comes back blank.

SELECT  *  
FROM Table1 WHERE MAKE+MODEL+[Serial Number] NOT IN
(SELECT make+model+[serial number] FROM Table2)

Table 1 has 5 records.

When I change the query to IN, I get 1 record. What am I doing wrong with the NOT?

Answer 1

It's because of the way NOT IN works.

To avoid these headaches (and for a faster query in many cases), I always prefer NOT EXISTS:

SELECT  *  
FROM Table1 t1 
WHERE NOT EXISTS (
    SELECT * 
    FROM Table2 t2 
    WHERE t1.MAKE = t2.MAKE
    AND   t1.MODEL = t2.MODEL
    AND   t1.[Serial Number] = t2.[serial number]);