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.

CodePudding user response：

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

CodePudding user response：

You can use where color= 'red' OR color is NULL to get the required result.