Alternative ways to include NULL values in the results while using SQL negation commands(!= or NOT IN) with Rails

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'")

I hope you enjoyed this article and learned other ways of dealing with NULL values. Feel free to share your feedback or suggestion. We would ❤️ to hear from you. Thank you.

References

• SQL COALESCE Function: Handling NULL Effectively
• SQL NULL Functions

Originally published at https://blog.kiprosh.com on December 22, 2020.