Forum Stats

  • 3,876,115 Users
  • 2,267,061 Discussions
  • 7,912,434 Comments

Discussions

Support "IS [ NOT ] DISTINCT FROM" syntax

User_1871
User_1871 Member Posts: 247 Red Ribbon

Edited. My previous version of this post was incorrect.


The IS [ NOT ] DISTINCT FROM syntax is supported in some databases, but not in Oracle:

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:

User_1871fac586William Robertson
3 votes

Active · Last Updated

Comments