in operator
Filters a record set for data with a case-sensitive string.
The following table provides a comparison of the in operators:
| Operator | Description | Case-Sensitive | Example (yields true) |
|---|---|---|---|
in |
Equals to any of the elements | Yes | "abc" in ("123", "345", "abc") |
!in |
Not equals to any of the elements | Yes | "bca" !in ("123", "345", "abc") |
in~ |
Equals to any of the elements | No | "Abc" in~ ("123", "345", "abc") |
!in~ |
Not equals to any of the elements | No | "bCa" !in~ ("123", "345", "ABC") |
Note
- In tabular expressions, the first column of the result set is selected.
- The expression list can produce up to
1,000,000values. - Nested arrays are flattened into a single list of values. For example,
x in (dynamic([1,[2,3]]))becomesx in (1,2,3).
For further information about other operators and to determine which operator is most appropriate for your query, see datatype string operators.
Performance tips
Note
Performance depends on the type of search and the structure of the data.
For faster results, use the case-sensitive version of an operator, for example, in, not in~. For best practices, see Query best practices.
Syntax
T | where col in (list of scalar expressions)
T | where col in (tabular expression)
Arguments
- T - The tabular input whose records are to be filtered.
- col - The column to filter.
- list of expressions - A comma-separated list of tabular, scalar, or literal expressions.
- tabular expression - A tabular expression that has a set of values. If the expression has multiple columns, the first column is used.
Returns
Rows in T for which the predicate is true.
Examples
Use in operator
StormEvents
| where State in ("FLORIDA", "GEORGIA", "NEW YORK")
| count
Output
| Count |
|---|
| 4775 |
Use dynamic array
let states = dynamic(['FLORIDA', 'ATLANTIC SOUTH', 'GEORGIA']);
StormEvents
| where State in (states)
| count
Output
| Count |
|---|
| 3218 |
Subquery
// Using subquery
let Top_5_States =
StormEvents
| summarize count() by State
| top 5 by count_;
StormEvents
| where State in (Top_5_States)
| count
The same query can be written as:
// Inline subquery
StormEvents
| where State in (
( StormEvents
| summarize count() by State
| top 5 by count_ )
)
| count
Output
| Count |
|---|
| 14242 |
Top with other example
let Lightning_By_State = materialize(StormEvents | summarize lightning_events = countif(EventType == 'Lightning') by State);
let Top_5_States = Lightning_By_State | top 5 by lightning_events | project State;
Lightning_By_State
| extend State = iif(State in (Top_5_States), State, "Other")
| summarize sum(lightning_events) by State
Output
| State | sum_lightning_events |
|---|---|
| ALABAMA | 29 |
| WISCONSIN | 31 |
| TEXAS | 55 |
| FLORIDA | 85 |
| GEORGIA | 106 |
| Other | 415 |
Use a static list returned by a function
StormEvents | where State in (InterestingStates()) | count
Output
| Count |
|---|
| 4775 |
The function definition.
.show function InterestingStates
Output
| Name | Parameters | Body | Folder | DocString |
|---|---|---|---|---|
| InterestingStates | () | { dynamic(["WASHINGTON", "FLORIDA", "GEORGIA", "NEW YORK"]) } |