ActiveRecord's where.not and nil

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 ActiveRecord’s 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.