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	If grouping from intermediate results, 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. If grouping from the corpus, EQL ignores records that have 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. Note that: Filters used directly on the corpus 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: Normal results Records for a NaN value 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.