where operator
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Filters a table to the subset of rows that satisfy a predicate.
The
where
andfilter
operators are equivalent
T | where
Predicate
Learn more about syntax conventions.
Name | Type | Required | Description |
---|---|---|---|
T | string |
✔️ | Tabular input whose records are to be filtered. |
Predicate | string |
✔️ | Expression that evaluates to a bool for each row in T. |
Rows in T for which Predicate is true
.
Nota
All filtering functions return false when compared with null values. Use special null-aware functions to write queries that handle null values.
Use simple comparisons between column names and constants. ('Constant' means constant over the table - so
now()
andago()
are OK, and so are scalar values assigned using alet
statement.)For example, prefer
where Timestamp >= ago(1d)
towhere bin(Timestamp, 1d) == ago(1d)
.Simplest terms first: If you have multiple clauses conjoined with
and
, put first the clauses that involve just one column. SoTimestamp > 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.
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.
DamageProperty | EventType | BeginLocation | EndLocation |
---|---|---|---|
5000 | Flood | FAYETTE CITY LOWBER | |
5000 | Flood | MORRISVILLE WEST WAYNESBURG | |
10000 | Flood | COPELAND HARRIS GROVE | |
5000 | Flood | GLENFORD MT PERRY | |
25000 | Flood | EAST SENECA BUFFALO AIRPARK ARPT | |
20000 | Flood | EBENEZER SLOAN | |
10000 | Flood | BUEL CALHOUN | |
10000 | Flood | GOODHOPE WEST MILFORD | |
5000 | Flood | DUNKIRK FOREST | |
20000 | Flood | FARMINGTON MANNINGTON |
The following query returns the rows in which the word "cow" appears in any column.
StormEvents
| where * has "cow"