Null Values vs. Blank Values
One of the most confusing aspects about database querying is understanding the difference between null values and blank values. You might have noticed that the operators dropdown list includes both the operators is blank and is null. That’s great, except for one thing: what’s the difference between a blank value and a null value?
To be honest, the answer to that question is perhaps more philosophical than it is practical. In database terms, however, a null value is a value that doesn’t exist: the field does not contain a value of any kind (not even a blank value). By contrast, a blank value is a real value: it just happens to be a string value containing 0 characters. Two scenarios are largely responsible for blank values:
- The user fails to complete a field on their registration form. For example, a user does not enter his or her country or phone number.
- Your registration form is configured to save empty fields as blank values rather than null values.
Fortunately, all you really need to know is this: blank values are possible only when working with strings. For example, click on an operator for any other datatype and you won’t see either the is blank or the is not blank operators listed:
Consequently, the blank/null distinction doesn’t apply if you’re working with datetime or numeric values. And what if you are working with string values and you want to filter out items that don’t seem to have a value? In that case, just filter out both blanks and nulls:
Do that and you’re covered regardless of whether that empty value is blank or null.