In the previous article, we went through How NOT IN works with NULL values. We also learned how we can overcome this restriction. In this article, we will look at alternative ways to handle NULL values with SQL functions.

So basically when we use != or NOT IN in query, it ignores the records with NULL values for fields.

User.where("state != ?", 'active')

SELECT * FROM users WHERE state != 'active'

OR

User.where("state NOT IN (?)", ['active'])

SELECT * FROM users WHERE state NOT IN ('active')

The above queries will consider records with state having a NOT NULL value, but state having a NULL value will not be considered. To consider state with the NULL value, we have to explicitly add the OR clause.

User.where("state != ? OR state IS NULL", 'active')

SELECT * FROM users WHERE state != 'active' OR state IS NULL

OR

User.where("state NOT IN (?) OR state IS NULL", ['active'])

SELECT * FROM users WHERE state NOT IN ('active') OR state IS NULL

Lets take a look at the alternatives for handling NULL values

  1. With COALESCE function - MySQL/SQL Server/SQLite/PostgreSQL/Oracle

    The COALESCE function returns the first non-NULL expression in the specified list. If all the arguments are NULL, then it will return NULL.

    COALESCE(expression, replacement1...n)

    Here, considering '' (blank string) for NULL value while performing query to return records with NULL value.

    User.where("COALESCE(state, '') != 'active'")

  2. With IFNULL function in MySQL/SQLite

    The MySQL IFNULL function returns an alternative value if an expression is NULL.

    IFNULL(expression, replacement)

    User.where("IFNULL(state, '') != 'active'")

  3. With ISNULL function in SQL Server

    The SQL Server ISNULL function returns an alternative value if an expression is NULL.

    ISNULL(expression, replacement)

    User.where("ISNULL(state, '') != 'active'")

  4. With NVL function in Oracle

    The Oracle NVL function returns an alternative value if an expression is NULL.

    NVL(expression, replacement)

    User.where("NVL(state, '') != 'active'")

  5. With IS DISTINCT FROM statement in PostgreSQL

    In PostgreSQL, we can use IS DISTINCT FROM to work around the problems of NULL, which treat NULL as a comparable value.

    attribute IS DISTINCT FROM value

    User.where("state IS DISTINCT FROM 'active'")
Availability of SQL NULL related functions

I hope you enjoyed this article and learned other ways of dealing with NULL values. Thank you for reading. ❤️

References