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

Question

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.

Have you (distinct) listed the records in each table and tried to the see if there are any that match? — Preet Sangha, Jun 07 '12 at 05:34
Also is `items.upc` NULLable? Are they the exact same data type? — Aaron Bertrand, Jun 07 '12 at 05:35
@PreetSangha Yes. The upc field in the items table is unique so no need to do so there, but originally I had distinct in the queries above when selecting from posrecords. Honestly, that's not relevant though as it wouldn't affect whether or not there are results returned; it would only eliminate duplicate results. That's why I removed it from the queries in my question to avoid any irrelevant comments about its use. — Brandon Moore, Jun 07 '12 at 05:40
@PreetSangha FYI, I already know there aren't any that match though. So there should only be results on the NOT IN query. — Brandon Moore, Jun 07 '12 at 05:41
@AaronBertrand One is varchar(14) and the other is varchar(50). Yes they are both nullable, so I guess I wasn't 100% accurate when I commented that the upc field in mrs..items was unique as there are multiple nulls, but it is otherwise unique when disregarding the nulls. — Brandon Moore, Jun 07 '12 at 05:45
You should avoid NOT IN ... especially when the columns are nullable. Why are the data types different? Is it possible that a padding issue is causing mismatch? What about collation, do both databases (and both UPC columns) use the same collation / codepage? Can you show some sample data so others can try and reproduce your problem? — Aaron Bertrand, Jun 07 '12 at 05:47
@AaronBertrand It was the nulls throwing off the NOT IN query. I didn't realize how a NOT IN expression was evaluated. — Brandon Moore, Jun 07 '12 at 05:58

Vishwanath Dalvi · Accepted Answer · 2012-06-07T05:57:51.623

3

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)

edited Jun 07 '12 at 05:57

answered Jun 07 '12 at 05:39

Vishwanath Dalvi

35,388
41
123
155

Okay, so I just realized that "select 1 where 1 in (null, 1, null)" will return 1, however "select 1 where 1 not in (null, 2, null)" will not return any results. Why is it that the nulls don't cause a problem when looking for matches, but they do matter when looking for the absense of matches? – Brandon Moore Jun 07 '12 at 05:52
Nevermind, just found this question that explains it: http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values – Brandon Moore Jun 07 '12 at 05:55

score 1 · Answer 2 · answered Jun 07 '12 at 05:49

1

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;

answered Jun 07 '12 at 05:49

Kshitij

8,474
2
26
34

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

2 Answers2