Edited. My previous version of this post was incorrect.
The IS [ NOT ] DISTINCT FROM syntax is supported in some databases, but not in Oracle:
IS NOT DISTINCT FROM (null-safe equals comparison) (0 Bytes)For example, this syntax works in SQLite: (IS NOT works the same way as IS NOT DISTINCT FROM )
--Select rows where COST1 is different than COST2.
--Treat nulls as if they are equal. Don't treat nulls as zeros.
with workorder (cost1,cost2) as (
values
( 100.00, 100.00),
(-100.00, null),
( null, 0),
( 0, 100.00),
( null, null)
)
select
*
from
workorder
where
cost1 is not cost2
Result:
cost1 cost2
------ ------
-100 null
null 0
0 100
db<>fiddle (SQLite 3.27)
Could that same functionality be added to Oracle?
To me, that would be more convenient and easier to read than the usual workarounds:
Succinct way to select where COST1 is different than COST2 (treat null=null)
Coding Around NULL Values