CODEWITHSUNDEEP
sqlsql-servert-sqlnotin
Madam Zu Zu
Madam Zu Zu

Reputation: 6615

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?

Upvotes: 36

Views: 143418

Answers (6)

Imtiaz
Imtiaz

Reputation: 11

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

Upvotes: 2

Kris Ivanov
Kris Ivanov

Reputation: 10598

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

Upvotes: 1

Shan Plourde
Shan Plourde

Reputation: 8726

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.

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97841

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

Upvotes: 47

Joe Stefanelli
Joe Stefanelli

Reputation: 135928

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

Upvotes: 6

Rebecca Chernoff
Rebecca Chernoff

Reputation: 22635

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.

Upvotes: 1

Related Questions