Null values

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel

All scalar data types in Kusto have a special value that represents a missing value. This value is called the null value, or null.

Note

The string data type doesn't support 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), timespan(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.

Note

Because the string type doesn't support null values, we recommend using 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).

For 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)

Output

val	IsBiggerThan3	IsBiggerThan3OrNull	IsEqualToNull	IsNotEqualToNull
5	true	true	false	true
null	null	true	null	null

Null values and aggregation functions

When applying the following operators to entities that include null values, the null values are ignored and don't factor into the calculation:

• count()
• count_distinct()
• countif()
• dcount()
• dcountif()
• make_bag()
• make_bag_if()
• make_list()
• make_list_if()
• make_set()
• make_set_if()
• stdev()
• stdevif()
• sum()
• sumif()
• variance()
• varianceif()

Null values and the where operator

The where operator use 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.

For example:

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

Output

ival	sval
null	b

Null values and binary operators

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 in Exceptions to this rule, 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".

Exceptions to this rule

• For the equality (==) and inequality (!=) operators, if one of the values is null and the other value isn't null, then the result is either bool(false) or bool(true), respectively.
• For the logical AND (&&) operator, if one of the values is bool(false), the result is also bool(false).
• For the logical OR (||) operator, if one of the values is bool(true), the result is also bool(true).

For example:

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

Output

val	Add	Multiply
5	15	50
null	null	null

Null values and the logical NOT (!) operator

The logical NOT operator not() yields the value bool(null) if the argument is the null value.

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.

Null values and data ingestion

For most data types, a missing value in the data source produces a null value in the corresponding table cell. However, columns of type string and CSV (or CSV-like) data formats are an exception to this rule, and a missing value produces an empty string.

For example:

.create table T(a:string, b:int)

.ingest inline into table T
[,]
[ , ]
[a,1]

T
| project a, b, isnull_a=isnull(a), isempty_a=isempty(a), stlen_a=strlen(a), isnull_b=isnull(b)

Output

a	b	isnull_a	isempty_a	strlen_a	isnull_b
		false	true	0	true
		false	false	1	true
a	1	false	false	1	false

Note

• If you run the above query in Kusto.Explorer, all true values will be displayed as 1, and all false values will be displayed as 0.
• Kusto doesn't offer a way to constrain a table's column from having null values. In other words, there's no equivalent to SQL's NOT NULL constraint.