Catering for NULLS in comparitive statements

Amazing how your SQL gets rusty, but here goes.

Have a test that evaluates whether a value has changed in a trigger. Basically, if it has changed I do something. It can be NULL so I have to test if its been changed from NULL to a value and vice versa, as well as checking if values existed before and after the change, but are different.

The old and new referred to below are the before and after values of the same column in a DB trigger.


I have two methods:

(1) IF
NVL(old,'XXXX') <> NVL(new,'XXXX') THEN ...
or

(2) IF
old <> new OR
(old IS NULL AND new IS NOT NULL) OR
(old IS NOT NULL AND new IS NULL) THEN ...

(1) would work but I don't like coding the NVL alternative. I mean what if the value really was 'XXXX'! (Highly unlikely)

(2) works but seems cumbersome.

Is there a better, recommended method?

How about creating a function (preferably three overloaded packaged functions for the three main datatypes - number, date, string) and implement whichever method you prefere in it. Then simply call that function with new and old values wherever you want to check if column vas changed.

I like the function call suggestion on the basis of readability etc. but the simple IS NULL logic is far more efficient. The most succinct expression of this logic i have seen is probably...

IF :OLD.column_name != :NEW.column_name OR (:OLD.column_name IS NULL) != (:NEW.column_name IS NULL) THEN...

Interestingly there is an undocumented function called sys_op_map_nonnull which is suitably overloaded and used (see also sys_op_descend, sys_op_countchg etc.) by DBMS_STATS for handling this comparison. Standard undocumented caveats apply.

IF sys_op_map_nonnull (:OLD.column_name) != sys_op_map_nonnull (:NEW.column_name) THEN ...

Padders

Excellent suggestions chaps.

And what do you think of using " <> " versus " != " ...?

I see '!=' used most commonly in PL/SQL hence I suspect that would be most readable in the sense that it is recognizable. Not that there is anything wrong with with '<>' and performance wise I would hope there is no difference.

Of course if you want to be different why not try '^=' or '~='?

Padders

'<>' is the only version given by my ANSI SQL 92 quick reference. I find it esthetically pleasing too.

'!=' is used in other langauges.

They behave identically in Oracle, so you pays your money and takes your choice.