Null Values

Null literals

The null value of a scalar type T is represented in the query language by the null literal T(null). The following query returns a single row full of null values:

print bool(null), datetime(null), dynamic(null), guid(null), int(null), long(null), real(null), double(null), time(null)

Predicates on null values

The scalar function isnull() can be used to determine if a scalar value is the null value. The corresponding function isnotnull() can be used to determine if a scalar value isn't the null value.

Because the string type doesn't support null values, it's recommended to use the isempty() and the isnotempty() functions.

Equality and inequality of null values

• Equality (==): Applying the equality operator to two null values yields bool(null). Applying the equality operator to a null value and a non-null value yields bool(false).
• Inequality (!=): Applying the inequality operator to two null values yields bool(null). Applying the inequality operator to a null value and a non-null value yields bool(true).

Example:

datatable(val:int)[5, int(null)]
| extend IsBiggerThan3 = val > 3
| extend IsBiggerThan3OrNull = val > 3 or isnull(val)
| extend IsEqualToNull = val == int(null)
| extend IsNotEqualToNull = val != int(null)

Null values and the where query operator

The where operator uses Boolean expressions to determine if to emit each input record to the output. This operator treats null values as if they're bool(false). Records for which the predicate returns the null value are dropped and don't appear in the output.

Example:

datatable(ival:int, sval:string)[5, "a", int(null), "b"]
| where ival != 5

Binary operators and null values

Binary operators are scalar operators that accept two scalar values and produce a third value. For example, greater-than (>) and Boolean AND (&&) are binary operators.

For all binary operators except as noted below, the rule is as follows:
If one or both of the values input to the binary operator are null values, then the output of the binary operator is also the null value. In other words, the null value is "sticky".

Example

datatable(val:int)[5, int(null)]
| extend Add = val + 10
| extend Multiply = val * 10

Null values and the in operator

• The in operator behaves like a logical OR of equality comparisons.
• The !in operator behaves like a logical AND of inequality comparisons.