Joe Morano Joe Morano - 11 months ago 54
MySQL Question

How to search for nil results with Rails SQL query

I have a model

with an
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 Source

Here's the syntax you're looking for:

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