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). Note, however, that COUNT(1) does count records with NULL values.
Boolean operators See Boolean operators.
Grouping expressions EQL does not ignore records that have a NULL value in any of the group keys, and considers the record to be present in a group. Even all-NULL groups are returned.
Filters When doing a comparison against a specific value, the NULL value will not match the specified filter, except for the IS NULL filter.
Note that:
  • Filters used directly on collections have the same semantics as filters on intermediate results.
  • NOT(x=y) is always equivalent to x<>y for all filters.
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 A
  • WHERE price <> 5 matches neither A nor B
  • WHERE NOT(price = 5) matches neither A nor B
  • WHERE price = 99 matches neither A nor B
  • WHERE NOT(price <> 99) matches neither A nor B
  • WHERE price <> 99 matches A
  • WHERE NOT(price = 99) matches A
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.