Skip to content

Latest commit

 

History

History
109 lines (78 loc) · 4.26 KB

where-operator.md

File metadata and controls

109 lines (78 loc) · 4.26 KB
title description ms.reviewer ms.topic ms.date monikerRange
where operator
Learn how to use the where operator to filter a table to the subset of rows that satisfy a predicate.
alexans
reference
08/11/2024
microsoft-fabric || azure-data-explorer || azure-monitor || microsoft-sentinel

where operator

[!INCLUDE applies] [!INCLUDE fabric] [!INCLUDE azure-data-explorer] [!INCLUDE monitor] [!INCLUDE sentinel]

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

The where and filter operators are equivalent

Syntax

T | where Predicate

[!INCLUDE syntax-conventions-note]

Parameters

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.

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.

Performance tips

  • Use simple comparisons between column names and constants. ('Constant' means constant over the table - so now() and ago() are OK, and so are scalar values assigned using a let statement.)

    For example, prefer where Timestamp >= ago(1d) to where 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. 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.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

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

Check if column contains string

The following query returns the rows in which the word "cow" appears in any column.

:::moniker range="azure-data-explorer"

[!div class="nextstepaction"] Run the query ::: moniker-end

StormEvents
| where * has "cow"

::: moniker range="microsoft-fabric || azure-data-explorer || azure-monitor || microsoft-sentinel"

Related content