CODEWITHSUNDEEP
mysqlsql
freebil
freebil

Reputation: 21

Select from where a value may be null or not

I have a query

select c from DTO c where trafficLight = ?1

When traffic light is red or green it returns the expected rows but when traffic light is null it returns nothing. In database there are records with null traffic light. I know that the checking for null is "is null", but what is the best way to do it here? Thanks.

Upvotes: 0

Views: 93

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35573

I want the expression "where trafficLight = ?1" to evaluate all the cases

"bad luck I'm afraid"

NULL is the absence of any value, so by definition you if use the equal operator against NULL it will NEVER return true:

where NULL = NULL -- is never true

as there is no value at all to compare, there simply is no way to know if they are equal or not, so "not equal" also does not work. I do understand this is difficult to follow, but "it is what it is" and try as you might NULL = NULL is never true. This however will work for all cases

where (trafficLight = ?1 and ?1 IS NULL)

where (trafficLight = 'red' or 'red' IS NULL)     -- true when trafficLight = 'red'
where (trafficLight = 'amber' or 'amber' IS NULL) -- true when trafficLight = 'amber'
where (trafficLight = 'green' or 'green' IS NULL) -- true when trafficLight = 'green'
where (trafficLight = NULL or NULL IS NULL)       -- true for all

Upvotes: 2

Related Questions