Joe Morano Joe Morano - 1 month ago 8
MySQL Question

How to search for nil results with Rails SQL query

I have a model

User
with an
Address
attribute. I want to run a query that includes users with nil Addresses.

The following query:

User.where(address: nil)


returns a correct result of 2. But this one:

User.where('address = ? AND address = ?', nil, nil)


returns 0.

Is there something wrong with my syntax in the second query?

Here's the SQL query actually being run in my server:

SELECT COUNT (*) FROM "users" WHERE (address = NULL AND address = NILL)


I suspect it's supposed to say "address IS NULL" not "address = NULL"

Answer

Here's the syntax you're looking for:

User.where('address IS ? AND address IS ?', nil, nil)