List:General Discussion« Previous MessageNext Message »
From:Harald Fuchs Date:March 16 2005 9:42am
Subject: Re: subquery fails when a NOT IN operator tests a subset with NULL valu
View as plain text  
In article <4237EF79.3090906@stripped>,
Giuseppe Maxia <g.maxia@stripped> writes:

> The whole point is actually in subqueries, not when using IN or NOT IN in a normal
> query.
> The bug occurs when a NOT IN is used in a subquery as a LEFT JOIN replacement.

> SELECT something from t1 where column1 NOT IN (SELECT nullable_column from t2);

That's not a bug.  Let's say that the subquery returns 2, NULL, 3.
Thus the NOT IN is a shorthand for

  column1 != 2 AND column1 != NULL AND column1 != 3

Since the second condition is never true, you get an empty result set.

Thread
subquery fails when a NOT IN operator tests a subset with NULL valuGiuseppe Maxia16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULL valuDan Nelson16 Mar
    • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluGiuseppe Maxia16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULL valuHarald Fuchs16 Mar
  • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluPeter Brawley16 Mar
    • Re: subquery fails when a NOT IN operator tests a subset with NULLvaluGiuseppe Maxia16 Mar