Handling NULL attribute values

If an attribute value is missing for a record, then the attribute is referred to as being NULL. For example, if a record does not contain an assignment for a Price attribute, EQL defines the Price value as NULL.

The following table outlines how EQL handles NULL values for each type of operation:
Type of operation How EQL handles NULL values
Arithmetic operations and non-aggregating functions The value of any operation on a NULL value is also defined as NULL.

For example, if a record has a value of 4 for Quantity and a NULL value for Price, then the value of Quantity + Price is considered to be NULL.

Aggregating functions EQL ignores records with NULL values.

For example, if there are 10 records, and 2 of them have a NULL value for a Price attribute, all aggregating operations ignore the 2 records, and instead compute their value using only the other 8 records.

If all 10 records have a NULL Price, then most aggregations such as SUM(Price) also result in NULL values.

The exceptions are COUNT and COUNTDISTINCT, which return zero if all the records have a NULL value. (That is, the output of COUNT or COUNTDISTINCT is never NULL.)

Grouping expressions EQL ignores any record that has a NULL value in any of the group keys, and does not consider the record to be present in any group.
Filters When doing a comparison against a specific value, the NULL value will not match the specified filter, except for the IS NULL filter.
For example, if record A has price 5, and record B has no price value, then:
  • WHERE price = 5 matches A
  • WHERE NOT(price = 5) matches B
  • WHERE price <> 5 matches neither A nor B
  • WHERE NOT(price <> 5) matches both A and B
  • WHERE price = 99 matches neither A nor B
  • WHERE NOT(price = 99) matches both A and B
  • WHERE price <> 99 matches A
  • WHERE NOT(price <> 99) matches B
Sorting
For any sort order specified, EQL returns:
  1. Normal results
  2. Records for a NaN value
  3. Records with a NULL value
Note: There is no NULL keyword or literal. To create a NULL, use CASE, as in this example: CASE WHEN False THEN 1 END.