Records with null excluded when searching for does-not-equal 0

Erin Wagner

I have a table with the property "status" that can be 0, 1 or null.

I want to pull all the records where status does not equal 0.

But it's only returning records where status=1. It doesn't include records where status=null.

Could that be tweaked to include records where the value is null so it's more intuitive?

[image.png]
[image.png]

Sean Montgomery

Hey ,

Sorry for the delay on this. Null is a special value in the SQL language. It actually is no value (kind of strange at first to hear that) but you can think of it as in a different bucket. So status != 0 would only work for values that are non-null and not 0. If you wanted not equal to zero and not null, then you would need to add an extra conditional.

Using a different operator tends to make this easier to understand. For example if you wanted user_plan objects where status > 0. Would that include null values? no it wouldn't. What about if it was status <= 0? Would it then? no because you cant compare a null value like that.

So to get this working, you would need to be a little bit more verbose in your conditional.

db:user_plan.user_id = input:user_id and (db:user_plan.status != 0 or db:user_plan.status = null)

Sean Montgomery

bonus info - there actually are two operators that technically would do what you want - "is distinct from" and "is not distinct from". This is definitely getting into fringe detail on the SQL language. This is basically an = and a != operator that deals with null values the way you want. Technically we could add another operator to allow this, but sometimes less is more. The more verbose method doesn't seem that bad and this seems like an infrequent action.

Erin Wagner

Hi Sean! Months later, I am finally circling back to this. (Not working on Gomigo again just yet, but finally responding to all my messages from over the summer.)

Thank you so much for the information And, clearly, no problem about the delayed response 😆