Issue with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue with query

Author

Topic

taunt
Posting Yak Master

128 Posts

Posted - 2014-03-21 : 16:34:30

Hello I have a table that I'm trying to compare UPC's and it will return any that aren't in the table. This is my query:


SELECT UPC
FROM  ITEMSTEMP
WHERE (UPC NOT IN
                (SELECT UPC
                FROM  Items))

Which will find nothing. Even though I can find UPC's that aren't in Items. When I switch it to this:


SELECT UPC
FROM  Items
WHERE (UPC NOT IN
                (SELECT UPC
                FROM  ITEMSTEMP))

It will find results. Why isn't it showing me the results from the first query? Both fields are the same type (nvarchar(15)), and I know that soe UPC's arn't in Items even though the result is null.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-21 : 16:49:50

This is probably because you have some rows in Items that have UPC = null. Change the query to


SELECT UPC
FROM  ITEMSTEMP t
WHERE NOT EXISTS (SELECT * FROM Items i WHERE i.UPC = t.UPC)

taunt
Posting Yak Master

128 Posts

Posted - 2014-03-21 : 17:47:23

Bingo! That worked like a charm. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources