Beware of using references to null values

published on NOV 1, 2018

I bet that you were working on a query that should return all records where a given column not equal to a given value. To better visualize it, let’s use sample Location model and city column. Let’s also insert some sample data:

Location.create!(city: 'Berlin')
Location.create!(city: 'New York')
Location.create!(city: nil)

Our goal

We want to fetch all locations where the city is not Berlin. It seems to be an easy task:

Location.where.not(city: 'Berlin')

However, above solution didn’t work properly because you get only one location object instead of two. The query didn’t return record where the city is blank.

The problem

It’s because a null value is something different than false. It may be confusing especially for Ruby developers. For example, given query would not return any records:

Location.where("city != NULL")

but looking at the query it should, right? We just want to get locations where the city is not null and it does return blank results. To fix is you can use modified version:

Location.where("city IS NOT NULL")

But let’s get back to our initial problem

The solution

If we want to pull our locations properly we have to explicitly tell that we don’t want locations in Berlin city but we also want locations where the city is not set. Our final query version is a little bit more complicated than before:

Location.where("city != 'Berlin' OR city IS NULL")

I hope that this quick tip would save a lot of time for you when you will be wondering next time why you didn’t get the results set that you want.

Tagged: Ruby Ruby on Rails

Cookies help us deliver our services. By using our services, you agree to our use of cookies.