Re: SQL Update query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/18/04 

Date: Wed, 18 Aug 2004 23:43:50 +0200

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)