Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Support "IS [ NOT ] DISTINCT FROM" syntax

User_1871Jul 2 2022 — edited Jul 4 2022

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

Comments
Post Details
Added on Jul 2 2022
3 comments
944 views