[sql] SQL Server - NOT IN

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?

One issue could be that if either make, model, or [serial number] were null, values would never get returned. Because string concatenations with null values always result in null, and not in () with null will always return nothing. The remedy for this is to use an operator such as IsNull(make, '') + IsNull(Model, ''), etc.

Use a LEFT JOIN checking the right side for nulls.

SELECT a.Id
FROM TableA a
LEFT JOIN TableB on a.Id = b.Id
WHERE b.Id IS NULL

The above would match up TableA and TableB based on the Id column in each, and then give you the rows where the B side is empty.

SELECT  *  FROM Table1 
WHERE MAKE+MODEL+[Serial Number]  not in
    (select make+model+[serial number] from Table2 
     WHERE make+model+[serial number] IS NOT NULL)

That worked for me, where make+model+[serial number] was one field name

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

SELECT [T1].*
FROM [Table1] AS [T1]
WHERE  NOT EXISTS (SELECT 
    1 AS [C1]
    FROM [Table2] AS [T2]
    WHERE ([T2].[MAKE] = [T1].[MAKE]) AND
        ([T2].[MODEL] = [T1].[MODEL]) AND
        ([T2].[Serial Number] = [T1].[Serial Number])
);