Not equal <> query excludes NULL values

Question

I'm having a problem with some SQL queries that I cant figure out.

SELECT * FROM MasterList WHERE Requested <> "Yes";

If I run the above query on my table I get 60 records returned. However There should be close to 300. I think the issue is, some of the records are just blank in the Requested field and others are NULL. But I would have thought NULL would still count as not equal to "Yes" wouldnt it? If not, is there a way around that without having to go back and 'blank' all the null fields?

Answer 1

Nulls are not counted in comparison, if you want null values to be returned then you need to execute the following query:

SELECT * FROM MasterList WHERE Requested <> "Yes" OR Requested IS NULL;

Answer 2

<=>
NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;  
    -> 1, 1, 0   
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;   
    -> 1, NULL, NULL  

in your case use:

SELECT * FROM MasterList WHERE not Requested <=> "Yes"