Query to find UPC's not in another table

Transact-SQL
1

Hello I'm trying to use this query:

SELECT UPC
FROM items
WHERE (UPC NOT IN
(SELECT UPC
FROM SDFD))

Both fields are the same nvarchar(15) and I know that there are UPC's that are in the items table that aren't in the SDFD table. What am I doing incorrect? Because the result query always brings a result of (0 row(s) affected). Any help would be great.

Thanks

2

If you have one NULL value returned from the query SELECT UPC FROM SDFD then you will never find a match.

A couple of notes:

  1. Schema qualify the tables
  2. Use an alias for the tables - and use the alias for all column references
  3. Use NOT EXISTS for this query which will avoid the NULL issue
SELECT i.UPC
FROM dbo.Items i
WHERE NOT EXISTS (SELECT * FROM dbo.SDFD s WHERE s.UPC = i.UPC);
3

Does the
'''sql
SELECT UPC
FROM SDFD

bit return any NULL values?  because NOT IN will fail if the list contains NULL.

You could test that theory by changing your code to
'''sql
WHERE (UPC NOT IN
(SELECT UPC
FROM SDFD
WHERE UPC IS NOT NULL
))

but I favour the NOT EXISTS approach, already suggested. EXISTS only has to find one matching row, whereas IN (SELECT ... will potentially make a complete list and THEN check if it matches.

I also tend to use an OUTER JOIN - but that's just down to personal preference. My reason for doing that is I can easily change it to report on this that DO exist, rather than those that DON'T - seems to come up a fair bit during debugging "Why doesn't this £$%^&* thing work?" !!

SELECT i.UPC
FROM dbo.Items i
    LEFT OUTER JOIN dbo.SDFD s 
        ON s.UPC = i.UPC
WHERE s.UPC IS NULL -- Comment this out to find what DOES match