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.
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:
|
Sorting |
For any sort order specified, EQL returns:
|