| Groups | Blog | Home |
sql server programming : Unexpected result from NOT IN
[quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > select * from @x where col1 not in (1,Null) > > Let's translate above: > > col1 not in (1,Null) > col1 <> 1 OR col1 <> NULL > > The comparison to NULL is UNKNOWN, meaning that that the whole (col1 <> 1 OR col1 <> NULL) is > UNKNOWN. And UNKNOWN in a WHERE clause is converted to FALSE [1], meaning that no rows can possibly > match that condition. > > [1] UNK in WHERE is converted to false, since if you have for example: > col = 23 > If any row have NULL in the col column, that row is not returned (i.e., UNK -> FALSE in WHERE > clause). > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > "Leila" <Lei...@hotpop.com> wrote in messagenews:ebpBy%23SBIHA.1356@TK2MSFTNGP03.phx.gbl... > > Hi, > > When I run this code, it returns "2" which is expected: > > > declare @x table(col1 int) > > insert @x select 1 > > insert @x select 2 > > select * from @x where col1 not in (1) > > > But when NULL is added to the list of IN, then "2" disappears from te resultset: > > > declare @x table(col1 int) > > insert @x select 1 > > insert @x select 2 > > select * from @x where col1 not in (1,Null) > > > What's the logic behind this? > > Thanks in advance, > > Leila Hi Tibor, with all due respect let me correct your post, col1 in (1,Null) means col1 = 1 OR col1 = NULL even though (col1 = NULL) is always unknown, the whole expression can be true but: col1 NOT in (1,Null) means NOT(col1 = 1 OR col1 = NULL) applying DeMorgan's law: (col1 <> 1 AND col1 <> NULL) and that is always unknown because (col1 = NULL) is always unknown.
Because col1 not in (<value list>) is eqivalent to a sequence of <>
comparisons of col1 to the values in the value list. That is col1 not in (1, 5, 27) is logically the same as col1 <> 1 AND col1 <> 5 AND col1 <> 27 So col1 not in (1,Null) is the same as col1 <> 1 AND col1 <> Null But no matter what value is in col1, col1<>Null is neither TRUE nor FALSE, it is "unknown" and when you AND "UNKNOWN" with something else, the rules are: FALSE AND UNKNOWN = FALSE TRUE AND UNKNOWN = UNKNOWN So when you check if 2 is not in (1,null) the result you get is UNKNOWN and the row is returned only if the result is TRUE. Tom [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:ebpBy%23SBIHA.1356@TK2MSFTNGP03.phx.gbl... > Hi, > When I run this code, it returns "2" which is expected: > > declare @x table(col1 int) > insert @x select 1 > insert @x select 2 > select * from @x where col1 not in (1) > > But when NULL is added to the list of IN, then "2" disappears from te > resultset: > > declare @x table(col1 int) > insert @x select 1 > insert @x select 2 > select * from @x where col1 not in (1,Null) > > What's the logic behind this? > Thanks in advance, > Leila >
I'd be hard pressed to explain the "logic", but that is the expected
behavior of NOT IN. Remember it and code for it is all I can suggest. Usually it is more of a problem with a subquery than a hard coded list of values, but those can be written as NOT EXISTS to avoid the problem. Roy Harvey Beacon Falls, CT [quoted text, click to view] On Tue, 2 Oct 2007 23:35:54 +0330, "Leila" <Leilas@hotpop.com> wrote:
>Hi, >When I run this code, it returns "2" which is expected: > >declare @x table(col1 int) >insert @x select 1 >insert @x select 2 >select * from @x where col1 not in (1) > >But when NULL is added to the list of IN, then "2" disappears from te >resultset: > >declare @x table(col1 int) >insert @x select 1 >insert @x select 2 >select * from @x where col1 not in (1,Null) > >What's the logic behind this? >Thanks in advance, >Leila
[quoted text, click to view]
> IS NOT ( x OR y ) always guaranteed to return false if one of the sides is > unknown ? The 3VL truth table for OR looks like this: p | q | p OR q ---------+----------+---------- TRUE | TRUE | TRUE TRUE | FALSE | TRUE TRUE | UNKNOWN | TRUE FALSE | FALSE | FALSE FALSE | TRUE | TRUE FALSE | UNKNOWN | UNKNOWN UNKNOWN | TRUE | TRUE UNKNOWN | FALSE | UNKNOWN UNKNOWN | UNKNOWN | UNKNOWN The NOT truth table is a little shorter: p | NOT p ---------+---------- TRUE | FALSE FALSE | TRUE UNKNOWN | UNKNOWN For (p OR q), where where either p or q is UNKNOWN, you'll always get either TRUE or UNKNOWN as a result. For NOT (p OR q), you'll always get the opposite: FALSE or UNKNOWN.
This is the reason why some subquery NOT IN return no records when you were
expecting results. If one of your subquery rows is NULL, then the same logic applies. IS NOT ( x OR y ) always guaranteed to return false if one of the sides is unknown ? In one way its good as it maintains consistency with the interchangeable NOT( x ) AND NOT( y ) but on the other hand its bad as it isn't the inverse of ( x OR y ) ! Mr Tea [quoted text, click to view] "Alex Kuznetsov" <alkuzo@gmail.com> wrote in message news:1191357737.478726.204890@w3g2000hsg.googlegroups.com... > On Oct 2, 3:20 pm, "Tibor Karaszi" > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > select * from @x where col1 not in (1,Null) >> >> Let's translate above: >> >> col1 not in (1,Null) >> col1 <> 1 OR col1 <> NULL >> >> The comparison to NULL is UNKNOWN, meaning that that the whole (col1 <> 1 >> OR col1 <> NULL) is >> UNKNOWN. And UNKNOWN in a WHERE clause is converted to FALSE [1], meaning >> that no rows can possibly >> match that condition. >> >> [1] UNK in WHERE is converted to false, since if you have for example: >> col = 23 >> If any row have NULL in the col column, that row is not returned (i.e., >> UNK -> FALSE in WHERE >> clause). >> -- >> Tibor Karaszi, SQL Server >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi >> >> "Leila" <Lei...@hotpop.com> wrote in >> messagenews:ebpBy%23SBIHA.1356@TK2MSFTNGP03.phx.gbl... >> > Hi, >> > When I run this code, it returns "2" which is expected: >> >> > declare @x table(col1 int) >> > insert @x select 1 >> > insert @x select 2 >> > select * from @x where col1 not in (1) >> >> > But when NULL is added to the list of IN, then "2" disappears from te >> > resultset: >> >> > declare @x table(col1 int) >> > insert @x select 1 >> > insert @x select 2 >> > select * from @x where col1 not in (1,Null) >> >> > What's the logic behind this? >> > Thanks in advance, >> > Leila > > Hi Tibor, > > with all due respect let me correct your post, > > col1 in (1,Null) > means > col1 = 1 OR col1 = NULL > > even though (col1 = NULL) is always unknown, the whole expression can > be true > > but: > > col1 NOT in (1,Null) > means > NOT(col1 = 1 OR col1 = NULL) > applying DeMorgan's law: > (col1 <> 1 AND col1 <> NULL) > > and that is always unknown because (col1 = NULL) is always unknown. > >
[quoted text, click to view]
> select * from @x where col1 not in (1,Null) Let's translate above: col1 not in (1,Null) col1 <> 1 OR col1 <> NULL The comparison to NULL is UNKNOWN, meaning that that the whole (col1 <> 1 OR col1 <> NULL) is UNKNOWN. And UNKNOWN in a WHERE clause is converted to FALSE [1], meaning that no rows can possibly match that condition. [1] UNK in WHERE is converted to false, since if you have for example: col = 23 If any row have NULL in the col column, that row is not returned (i.e., UNK -> FALSE in WHERE clause). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Leila" <Leilas@hotpop.com> wrote in message news:ebpBy%23SBIHA.1356@TK2MSFTNGP03.phx.gbl...
> Hi, > When I run this code, it returns "2" which is expected: > > declare @x table(col1 int) > insert @x select 1 > insert @x select 2 > select * from @x where col1 not in (1) > > But when NULL is added to the list of IN, then "2" disappears from te resultset: > > declare @x table(col1 int) > insert @x select 1 > insert @x select 2 > select * from @x where col1 not in (1,Null) > > What's the logic behind this? > Thanks in advance, > Leila >
Hi Alex,
[quoted text, click to view] > with all due respect let me correct your post, > ... Note to self: Don't post when in a hurry, and when mind is already somewhere else (girlfriend was calling me...). Yes, you are right of course. Thanks. :-) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] "Alex Kuznetsov" <alkuzo@gmail.com> wrote in message
news:1191357737.478726.204890@w3g2000hsg.googlegroups.com... > On Oct 2, 3:20 pm, "Tibor Karaszi" > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > select * from @x where col1 not in (1,Null) >> >> Let's translate above: >> >> col1 not in (1,Null) >> col1 <> 1 OR col1 <> NULL >> >> The comparison to NULL is UNKNOWN, meaning that that the whole (col1 <> 1 OR col1 <> NULL) is >> UNKNOWN. And UNKNOWN in a WHERE clause is converted to FALSE [1], meaning that no rows can >> possibly >> match that condition. >> >> [1] UNK in WHERE is converted to false, since if you have for example: >> col = 23 >> If any row have NULL in the col column, that row is not returned (i.e., UNK -> FALSE in WHERE >> clause). >> -- >> Tibor Karaszi, SQL Server >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi >> >> "Leila" <Lei...@hotpop.com> wrote in messagenews:ebpBy%23SBIHA.1356@TK2MSFTNGP03.phx.gbl... >> > Hi, >> > When I run this code, it returns "2" which is expected: >> >> > declare @x table(col1 int) >> > insert @x select 1 >> > insert @x select 2 >> > select * from @x where col1 not in (1) >> >> > But when NULL is added to the list of IN, then "2" disappears from te resultset: >> >> > declare @x table(col1 int) >> > insert @x select 1 >> > insert @x select 2 >> > select * from @x where col1 not in (1,Null) >> >> > What's the logic behind this? >> > Thanks in advance, >> > Leila > > Hi Tibor, > > with all due respect let me correct your post, > > col1 in (1,Null) > means > col1 = 1 OR col1 = NULL > > even though (col1 = NULL) is always unknown, the whole expression can > be true > > but: > > col1 NOT in (1,Null) > means > NOT(col1 = 1 OR col1 = NULL) > applying DeMorgan's law: > (col1 <> 1 AND col1 <> NULL) > > and that is always unknown because (col1 = NULL) is always unknown. > >
Hi,
When I run this code, it returns "2" which is expected: declare @x table(col1 int) insert @x select 1 insert @x select 2 select * from @x where col1 not in (1) But when NULL is added to the list of IN, then "2" disappears from te resultset: declare @x table(col1 int) insert @x select 1 insert @x select 2 select * from @x where col1 not in (1,Null) What's the logic behind this? Thanks in advance, Leila
[quoted text, click to view]
On Tue, 02 Oct 2007 16:30:47 -0400, Roy Harvey (SQL Server MVP) wrote: >I'd be hard pressed to explain the "logic", but that is the expected >behavior of NOT IN. Remember it and code for it is all I can suggest. Hi Roy, I recently took a shot at explaining the logic in my blog, in the third part of my four-part series about NULL. You can find it at the URL below. For a proper understanding, I encourage you to read the two previous parts first. First part (what NULL is, what it isn't, and why even great names such as Codd and Date got it wrong): http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx Second part (how NULL invalidates two-valued logic and how three-valued logic solves this): http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx Third part (some curious side-effects of three-valued logic, such as how NULL affects NOT IN, explained): http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx Fourth part (NULL related functions): http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx -- Hugo Kornelis, SQL Server MVP
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |