Relational algebra and qualifications involving NULL values
All searching in the database uses the SQL language, which is based on the concepts of relational algebra. The following example illustrates how these concepts apply to the NULL value.
Suppose there is a form with a selection field called Field X, which allows two possible states: Yes or No. The form contains 20 records of which Field X is set to Yes for 7 of them, No for 8 of them, and NULL for 5 of them. When you search for the records, you receive the following results:
- Search for all tickets (without any conditions) results in 20 records returned.
- Search for all tickets where 'Field X' = NULL results in 5 records returned.
- Search for all tickets where 'Field X' = "Yes" results in 7 records returned.
- Search for all tickets where 'Field X' = "No" results in 8 records returned.
If you now search for all tickets where 'Field X' != "Yes", you might expect to receive 13 records returned (8 records where the field is No and 5 where it is NULL ) but you receive only 8 records. This is correct behavior according to the rules of relational algebra. NULL is NULL ; that is, it has no value. You must explicitly look for NULL values as they are not implicitly included in queries that search for values.
Relational algebra does not follow Boolean logic, where conditions are either true or false. Instead, every condition evaluates as one of TRUE, FALSE, or UNKNOWN. This is called three valued logic. The result of a comparison is UNKNOWN if either value being compared is the NULL value. Rows satisfy a search condition if the result of the condition is TRUE. Rows for which the condition is UNKNOWN do not satisfy the search condition.
The only way to find a row with a NULL value for a field in a search is to explicitly search for whether the field has a NULL value. Testing for NULL is the only case that a NULL value matches. So, in the example, to find all entries that are not Yes or are NULL, the qualification is
'Field X' != "Yes" OR 'Field X' = NULL
These criteria find the 13 records in the example data set.
Any comparison other than equal to and not equal to results in a value of UNKNOWN. In AND operations, at least one item is NOTTRUE, so the qualification does not succeed. In OR operations, this clause with NULL is UNKNOWN, so the qualification depends on the result of the other clauses. If all clauses evaluate to UNKNOWN, the qualification fails as a qualification.
If a NULL value is involved in an arithmetic operation, the result of the operation is NULL. In other words, any time a NULL value is involved, the entire operation becomes NULL.
As an example for this functionality, look at the following qualification
('Field A' = 5) OR ('Field B' > 'Field C' + 37)
If Field C has a NULL value, the second clause evaluates to UNKNOWN. Since the operation is an OR, the result of the qualification depends on whether Field A is set to 5 (success) or not (failure).
An alternative to using NULL is to have a value or state that represents unknown. Then, you always assign the value of Yes, No, or Unknown. Filters can be used to assign Unknown if the field is NULL. Then, the field always has a value and you do not have the issue about working with a NULL value.