Re: SQL Update query

From: Tom (Tom_at_discussions.microsoft.com)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 06:37:04 -0700

Hi Hugo,

Thank you for detailed explaination.

Thomas

"Hugo Kornelis" wrote:

> On Wed, 18 Aug 2004 14:09:01 -0700, Tom wrote:
>
> >Hi Vishal,
> >
> >Thanks, it worked without any issues. Just to check with you, what was the
> >difference between NOT IN and NOT exists. In the new update you passed had
> >NOT exists clause and it worked. Can you pls clarify to resolve the doubts.
> >
> >Thanks
> >Tom
>
> Hi Tom,
>
> PMFJI. The difference is that NOT IN gives unexpected results when the
> subselect has NULL values. Well unexpected - let's say unexpected by some,
> but correct given the nature of NULL. But undesirable in your case.
>
> Let's look at a simple expression. Copy and paste the following code in
> Query Analyzer, run it and be surprised:
> IF 3 NOT IN (1, NULL)
> PRINT 'True'
> ELSE
> PRINT 'False'
> IF NULL IN (1, NULL)
> PRINT 'True'
> ELSE
> PRINT 'False'
>
> Why do these expression evaluate to false? Let's start with the first
> expression. The ISO/ANSI standard for SQL says that an expression "a NOT
> IN (b1, b2, ...)" should return the same as "a <> b1 AND a <> b2 AND ..."
> (not in these exact words, but this is what the ANSI/SQL definition
> amounts to). So "3 NOT IN (1, NULL)" is equal to "3 <> 1 AND 3 <> NULL".
> The first part is obviously true, the second part involves comparison to
> NULL and is therefore (using three-valued logic) unknown. Again using
> three-valued logic, "true AND unknown" evaluates to unknown; therefore the
> IF part will not be executed and the ELSE part will. Obviously, the PRINT
> statement should have read "PRINT 'False or unknown'".
>
> The second expression: Let's start with ISO/ANSI again: "a IN (b1, b2,
> ....)" should be equal to "a = b1 OR a = b2 ..."; in this case the
> equivalent expression is "NULL = 1 OR NULL = NULL". Both the first part
> and the second part are unknown; the end result will be "unknwon OR
> unknown" --> "unknown".
>
> Things to remember:
> a) never use NOT IN if the subselect might include NULL. Best to be on the
> safe side and simple never use NOT IN with a subselect, but use NOT EXISTS
> instead.
> b) if you choose to disregard a), then remember that NULL IN (...) and
> NULL NOT IN (...) will always evaluate to unknown; something (other than
> NULL) NOT IN (...) will always evaluate to unknown or false if the
> subselect includes one or more NULL values; something (other than NULL) IN
> (...) doesn't behave unexpected when the subselect has NULLs.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>