Why do NULL fields get called equal?

I'm writing triggers to detect changes in fields in a database, and it appears I have to do really obnoxious things like

(SELECT SalesPrice FROM __old) <> (SELECT SalesPrice FROM __new)
or ((SELECT SalesPrice FROM __old) IS NULL and (SELECT SalesPrice FROM __new) IS NOT NULL)
or ((SELECT SalesPrice FROM __old) IS NOT NULL and (SELECT SalesPrice FROM __new) IS NULL)

rather than just

(SELECT SalesPrice FROM __old) <> (SELECT SalesPrice FROM __new)

to accurately detect if a field changed.开发者_如何学Python

Am I missing something, or does Advantage effectively claim that NULL == any value? Is there a good reason for this behavior? Is this some weird thing in the SQL definition? Is there a more succinct way this that doesn't do 3 checks in place of one?

This is unfortunately how SQL works with NULL values. NULL is not equal to anything, it is UNKNOWN. For example, somevalue == NULL -> unknown somevalue <> NULL -> unknown

As a result it will never pass a "true" check Null Values - Wikipedia

There are a couple of options: A) Do not allow null values (I recommend combining this with a default value) B) Use IFNULL to set the field to some value such as

(SELECT IFNULL(SalesPrice, -9999) FROM __OLD) <> (SELECT IFNULL(SalesPrice, -9999) FROM __NEW)

But I don't know if I necessarily like this since a value must be picked that would not be valid.

In SQL, NULL does not compare to anything, except the IS [NOT] NULL expression. If I understand you question correctly, the problem here is that NULL must equal to NULL. If that is the case, the check may be simplified to:

( SELECT CASE WHEN n.SalesPrice IS NULL and o.SalePrice IS NULL THEN TRUE
         ELSE n.SalesPrice = o.SalesPrice END
  FROM __old o, __new n )