---
title: ActiveRecord's where.not and nil
teaser: 'ActiveRecord''s where.not has some potentially surprising behavior when it
  comes to null values.

  '
tags: rails,web
author: Stephen Hanson
published_on: 2018-06-22
---

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:

```ruby
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:

```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`:

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

To achieve this query in Rails, we can use ActiveRecord's `or`:

```ruby
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][avoid-three-state-bool], it's a little easier to handle `NULL`. Instead
of:

```ruby
User.where.not(subscribed: true)
```

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

```ruby
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][where not] or [this issue thread][rails-issue-where-not] 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.

[where not]: https://thoughtbot.com/blog/activerecords-wherenot
[avoid-three-state-bool]: https://thoughtbot.com/blog/avoid-the-threestate-boolean-problem
[rails-issue-where-not]: https://github.com/rails/rails/issues/19262