ActiveRecord's where.not and nil

By Stephen Hanson??June 22, 2018?UPDATED ON?March 6, 2019

ActiveRecord’s?where.not?is a great tool that can make complex queries easier to read. I’m definitely a fan, but I ran into some behavior that surprised me the other day and wanted to share what I found.

In my case, which I’ve slightly modified for this post, I had a?users?table with a?favorite_animal?column, which could potentially be?NULL. I wanted to query for all users who didn’t have a strong affinity for snakes, so I put together the following:

User.where.not(favorite_animal: "snake")        

Can you see the bug above? I didn’t initially. What I didn’t realize is that ActiveRecord’s?where.not?translates almost directly into a?!=?query in the database. The above fired off this SQL:

select * from users where favorite_animal != 'snake'        

In SQL databases,?NULL?is treated differently from other values, so?!=?queries never match columns that are set to?NULL. The fix in SQL is to explicitly also query for?NULL:

select * from users where favorite_animal IS NULL OR favorite_animal != 'snake'        

To achieve this query in Rails, we can use ActiveRecords?or:

User.where.not(favorite_animal: "snake")
  .or(User.where(favorite_animal: nil))        

If you are working with a boolean that can be nullable, which is?often a bad choice, it’s a little easier to handle?NULL. Instead of:

User.where.not(subscribed: true)        

to include?NULL, just use a regular?where:

User.where(subscribed: [nil, false])        

The takeaway: When using?where.not, be explicit if you want?NULL?values.

What’s next

If you found this post helpful, I recommend checking out our post on?ActiveRecord’s where.not?or?this issue thread?in the Rails GitHub repo which goes into some detail around why the decision was made to not have?where.not?match null by default.