NULL-aware comparison: IS [NOT] DISTINCT FROM

[michaelfranzl]

Hi, thank you for Sequel! Would it make sense to add the NULL-aware comparison IS [NOT] DISTINCT FROM, so that it is not necessary to fall back to Sequel.lit?

Quoting from https://modern-sql.com/feature/is-distinct-from :

The is [not] distinct from predicate was introduced in two steps: SQL:1999 added T151, “DISTINCT predicate”. The optional negation with not was added by SQL:2003 as feature T152, “DISTINCT predicate with negation”.

It is not widely supported (Postgres does).

Thanks!

[jeremyevans]

It's certainly possible to add this to Sequel. I've known about it for a long time, but this is the first request I've received for it. It's possible to emulate support on databases that don't natively support it using a case statement, so it's a feature that Sequel could support on all databases. It would probably be best to add it as a extension, considering it's not a feature that many people would be using.

[tycooon]

In one of my projects we have the following method:

  def distinct_from_expr(left_expr, right_expr)
    DB["? IS DISTINCT FROM ?", left_expr, right_expr]
  end

Then you can use it as MyModel.where(distinct_from_expr(:column_name, true)) for example.