exclude_or_null.rb

The exclude_or_null extension adds Dataset#exclude_or_null and Dataset#exclude_or_null_having. These methods are similar to Dataset#exclude and Dataset#exclude_having, except that they will also exclude rows where the condition IS NULL.

DB[:table].exclude_or_null(foo: 1)
# SELECT * FROM table WHERE NOT coalesce((foo = 1), false)

DB[:table].exclude_or_null{foo(bar) =~ 1}
# SELECT * FROM table HAVING NOT coalesce((foo(bar) = 1), false))

You can load this extension into specific datasets:

ds = DB[:table]
ds = ds.extension(:exclude_or_null)

Or you can load it into all of a database’s datasets, which is probably the desired behavior if you are using this extension:

DB.extension(:exclude_or_null)

Note, this extension works correctly on PostgreSQL, SQLite, MySQL, H2, and HSQLDB. However, it does not work correctly on Microsoft SQL Server, Oracle, DB2, SQLAnywhere, or Derby.

Related module: Sequel::ExcludeOrNull