At 04:55 AM 3/04/2003 +0000, you wrote:

>Hi Marco,
>thanks for your reply.
>
>Just checking something with your description below:
>
>(assuming A is old.VAL & B is new.VAL)
>
>What if A was NULL & B was not?
>This would not then evaluate to true, as A=B won't work, as one of
>the values is NULL,
>and ((A is null) and (B is null)) would not evaluate as one of the
>values is not NULL.
>
>Am I correct here?
>
>Is the solution to also include checks like:
>((A IS NULL) and (NOT B IS NULL))
>
>I find it a little cumbersome to have to evaluate so many
>conditions, when it seems logical to me that even though NULL is a
>state, not a value, clearly '7' and NULL are not equal...

No, it's not true that '7' and NULL are not equal. Under the rules of
logic (which SQL follows), NULL represents no value. It is unknown, has no
existent value. Even NULL==NULL evaluates to false. The only deduction
that can be made is that '7' is '7' (and thus, is not null) and that NULL
is not (NOT NULL).

If you have a personal aversion to NULL, set values that *your* logic works
with. For example, make an integer column non-nullable and default it to
-1 (or whatever suits you). You have to decide for yourself whether the
extra overhead of non-nullability and defaults is justified by your saving
a couple of lines of one-time code..

btw

B IS NOT NULL is more elegant than
NOT (B IS NULL)

just as

A <> B is more elegant than
NOT (A=B)

Keep the NOT(some predicate) for complex expressions.

Helen