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?
6 Answers
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]);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 NULLSELECT * 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
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 NULLThe 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.
1SELECT [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])
);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.
