CODEWITHSUNDEEP
sql-server-2008
Brandon Moore
Brandon Moore

Reputation: 8790

What's wrong with this "IN" sql expression? Doesn't work as expected

Found the best answer to my question here: NOT IN clause and NULL values

Neither of the following two queries return any results:

select upc 
from   staging..posrecords 
where  upc not in (select upc from mrs..items)

select upc 
from   staging..posrecords 
where  upc in (select upc from mrs..items)

Both of the following queries do return results:

select upc from staging..posrecords

select upc from mrs..items

Given that the latter two queries do both return results, I don't understand how it's possible that neither of the first two queries return any results whatsoever. Maybe it's late and I'm just missing something really obvious, but I'm about as stumped as I can be right now.

Furthermore, the following query also does returns results

select upc 
from mrs..items 
where upc not in (select upc from staging..posrecords)

That being the case, I am even more baffled as to why the very first query up above doesn't return any results.

Upvotes: 0

Views: 66

Answers (2)

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36681

Reason: When the subquery contains NULL values in a column it always returns NULL.

1 OR 2 OR NULL
TRUE OR NULL
NULL

More Details with example.

http://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/

The reason could be you have NULL values in upc column in mrs...items table, Try following query.

    select upc 
    from   staging..posrecords 
    where  upc not in (select upc from mrs..items where upc is not null)

    select upc 
    from   staging..posrecords 
    where  upc in (select upc from mrs..items where upc is not null)

Try using Not exists

    select upc 
    from staging..posrecords 
    where not exists ( select upc from mrs..items 
    where mrs..items.ups = staging..posrecords.upc)


    select upc 
    from   staging..posrecords 
    where not exists(select upc from mrs..items 
    where mrs..items.upc=staging..posrecords.upc)

Upvotes: 3

Kshitij
Kshitij

Reputation: 8634

Maybe its just your bad day. Try to get your very first NOT IN query using JOIN instead. like this - 

SELECT sp.upc 
FROM staging..posrecords sp LEFT JOIN mrs..items mi 
 ON (sp.upc=mi.upc)
WHERE
 mi.upc IS NULL;

Upvotes: 1

Related Questions