where operator

Filters a table to the subset of rows that satisfy a predicate.

The where and filter operators are equivalent

Syntax

T | where Predicate

Parameters

Returns

Rows in T for which Predicate is true.

[!NOTE] All filtering functions return false when compared with null values. Use special null-aware functions to write queries that handle null values.

• isnull()
• isnotnull()
• isempty()
• isnotempty()

Performance tips

• Use simple comparisons between column names and constants. ('Constant' means constant over the table - so now() is OK. ago() will be a specific function according to your database.

  For example, prefer where Timestamp >= minus(now(), toDayInterval(1d)) when using Clickhouse

• Simplest terms first: If you have multiple clauses conjoined with and, put first the clauses that involve just one column. So Timestamp > ago(1d) and OpId == EventId is better than the other way around.

For more information, see the summary of available String operators and the summary of available Numerical operators.

Examples

Order comparisons by complexity

The following query returns storm records that report damaged property, are floods, and start and end in different places.

Notice that we put the comparison between two columns last, as the where operator can't use the index and forces a scan.

StormEvents
| project DamageProperty, EventType, BeginLocation, EndLocation
| where DamageProperty > 0
    and EventType == "Flood"
    and BeginLocation != EndLocation 

The following table only shows the top 10 results. To see the full output, run the query.