I can’t figure out how to do a filter in MySQL for a value or NULL?
Here’s a simple contrived example. We have a sports team. Multiple tables. Everybody is in the process or ordering a t-shirt. And for a contrived use case, the t-shirt manufacturer says red shirts are no longer available. We want to order a new t-shirt for everyone on the team who hasn’t yet ordered or had an order for a red shirt. And after a join, I see the following:
Post join result showing name and color choice (less any filtering):
Adam red
Beth blue
Craig NULL
Dawn NULL
Edith red
Frank blue
Gabriel NULL
As part of that join, I’d really like to have just the result showing folks with red or NULL choices. I’m doing the test sql joins and WHERE filtering in a phpmyadmin panel in the browser. The "NULL" values show up as italic characters in lighter font color.
I’ve tried every combination of ...WHERE 'color' = "red" or 'color' = NULL
, also ...WHERE 'color' = "red" or 'color' = ""
that I can come up with. Generally I get a empty set of values as a result. Obviously, in the above example, I’d expect to see a list containing just Adam, Craig, Dawn, Edith, and Gabriel
in my query results.
Is there a trick here? Note, my actual joins are a little bit involved and the data is confidential.
2
Answers
You can use IS NULL to check the null values and To filter them out, you can use IS NOT NULL
here is the reference link: https://www.w3schools.com/sql/sql_null_values.asp
You can use
where color= 'red' OR color is NULL
to get the required result.