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]); 

Answer 2

You're probably better off comparing the fields individually, rather than concatenating the strings.

SELECT t1.*     FROM Table1 t1         LEFT JOIN Table2 t2             ON t1.MAKE = t2.MAKE                 AND t1.MODEL = t2.MODEL                 AND t1.[serial number] = t2.[serial number]     WHERE t2.MAKE IS NULL