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?

Khmer

Do you have any null values in your tables? – Alfred

maybe posting your data will help. the syntax looks ok. (although the psuedo-key is kinda funny) – Hephzibah

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

Epistaxis

See my edits (I improved my answer after reading Joe's good answer) – Epistaxis

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

Roxanneroxburgh

@xrum: With indexes on the columns, I would certainly think so. – Roxanneroxburgh

One caveat (which I mentioned to Rebecca) is that t2.MAKE has to be a non-nullable column, or at least needs to be a column without nulls in it if you want to get correct results 100% of the time. – Epistaxis

@Dave, MySQL has null_able comparison <=>, where null <=> null is true (as is 1 <=> 1). If sql-server has something simular, you can use that in your query. or use an if or case-when to handle the null values for MAKE. – Entryway

@Johan: That still won't solve the issue when using an anti-semi join as shown above if MAKE is potentially NULL in table t2. – Epistaxis

@Dave: left join on (coalesce(t1.make,0) = coalesce(t2.make,0)) won't work? – Entryway

@Johan: Not necessarily. You'd have to be very sure that there are no zeroes in the data of either table, otherwise you might be joining a NULL value to a zero inadvertently, which isn't what you want. Also, by using COALESCE in your join predicate, it's possible that you may not be able to use indexes on the "make" column effectively. So while this is a fine solution if "make" is not nullable, I prefer the NOT EXISTS solution for consistency and clarity. – Epistaxis

Answer 3

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

Lindner

Answer 4

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.

Rolanda

This usually works, but IMO isn't a good general solution to the problem because it can result in duplicate rows for TableA if multiple rows exist in TableB for the join predicate, and if b.Id happens to be a nullable column, you can get a false positive. It's a valid solution to this problem in many cases, though (depending on the data) – Epistaxis

Answer 5

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

Merth

Answer 6

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.

Bandoleer