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)
We want to fetch all locations where the city is not Berlin. It seems to be an easy task:
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.
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
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.