-
3
- #1
Earlier in the week there was a discussion about NOT IN and NOT EXISTS. (I believe it was thread183-272715) That discussion reminded me of a problem with NOT IN that many people know about, but might be worth refreshing.
What do you think this will do?
[tt]
If 'Coconut' NOT IN ('Apple', 'Banana')
Print 'Return True: No, was not found in list'
Else
Print 'Return False: Yes, was found in list'
[/tt]
Of course, it returns:
Return True: No, was not found in list, because Coconut is not in the list.
What do you think this will do?
[tt]
If 'Coconut' NOT IN ('Apple', 'Banana', NULL)
Print 'Return True: Was not found in list'
Else
Print 'Return False: Was found in list'
[/tt]
Surprisingly, it returns:
Return False: Was found in list.
Apparently, the NULL in the IN values will cause the Select to return something different than what you expect. (At the root of this is the problem of comparing anything to NULL; you will always get 'not equal').
As I have presented it so far, this may seem like a trivial problem. But if you have something like this:
[tt]
Select * from tblProducts
Where ProductId NOT IN
(Select ProductId from tblSales)
[/tt]
then you might get unexpected results, and your query will not work properly, if the subquery returns any NULL values in its list.
I'm not sure if this is actually considered a 'bug' or not, but it certainly is unexpected behavior for most of us. One thing you could do to avoid this problem is to add some code to make sure the subquery returns no NULL values:
[tt]
Select * from tblProducts
Where ProductId NOT IN
(Select ProductId from tblSales
WHERE ProductId IS NOT NULL)
[/tt]
Perhaps even better would be to avoid NOT IN completely, by redesigning your query to use NOT EXISTS, or JOINS, instead of NOT IN.
-------------
bperry
What do you think this will do?
[tt]
If 'Coconut' NOT IN ('Apple', 'Banana')
Print 'Return True: No, was not found in list'
Else
Print 'Return False: Yes, was found in list'
[/tt]
Of course, it returns:
Return True: No, was not found in list, because Coconut is not in the list.
What do you think this will do?
[tt]
If 'Coconut' NOT IN ('Apple', 'Banana', NULL)
Print 'Return True: Was not found in list'
Else
Print 'Return False: Was found in list'
[/tt]
Surprisingly, it returns:
Return False: Was found in list.
Apparently, the NULL in the IN values will cause the Select to return something different than what you expect. (At the root of this is the problem of comparing anything to NULL; you will always get 'not equal').
As I have presented it so far, this may seem like a trivial problem. But if you have something like this:
[tt]
Select * from tblProducts
Where ProductId NOT IN
(Select ProductId from tblSales)
[/tt]
then you might get unexpected results, and your query will not work properly, if the subquery returns any NULL values in its list.
I'm not sure if this is actually considered a 'bug' or not, but it certainly is unexpected behavior for most of us. One thing you could do to avoid this problem is to add some code to make sure the subquery returns no NULL values:
[tt]
Select * from tblProducts
Where ProductId NOT IN
(Select ProductId from tblSales
WHERE ProductId IS NOT NULL)
[/tt]
Perhaps even better would be to avoid NOT IN completely, by redesigning your query to use NOT EXISTS, or JOINS, instead of NOT IN.
-------------
bperry
