in operator

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

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

Nested arrays are flattened into a single list of values. For example, x in (dynamic([1,[2,3]])) becomes x 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.

Case-insensitive operators are currently supported only for ASCII-text. For non-ASCII comparison, use the tolower() function.

Performance tips

Note

When more than 128 search terms are used, text index lookup optimization is disabled, which might lead to reduced query performance.

Note

Performance depends on the type of search and the structure of the data. For best practices, see Query best practices.

Syntax

T | where col in (expression, ... )

Learn more about syntax conventions.

Parameters

Name Type Required Description
T string ✔️ The tabular input to filter.
col string ✔️ The column by which to filter.
expression scalar or tabular ✔️ An expression that specifies the values for which to search. Each expression can be a scalar value or a tabular expression that produces a set of values. If a tabular expression has multiple columns, the first column is used. The search considers up to 1,000,000 distinct values.

Returns

Rows in T for which the predicate is true.

Examples

List of scalars

The following query shows how to use in with a list of scalar values.

Run the query

StormEvents 
| where State in ("FLORIDA", "GEORGIA", "NEW YORK") 
| count

Output

Count
4775

Dynamic array

The following query shows how to use in with a dynamic array.

Run the query

let states = dynamic(['FLORIDA', 'ATLANTIC SOUTH', 'GEORGIA']);
StormEvents 
| where State in (states)
| count

Output

Count
3218

Tabular expression

The following query shows how to use in with a tabular expression.

Run the query

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 with an inline tabular expression statement.

Run the query

StormEvents 
| where State in (
    StormEvents
    | summarize count() by State
    | top 5 by count_
    ) 
| count

Output

Count
14242

Top with other example

The following example identifies the top five states with lightning events and uses the iff() function and in operator to classify lightning events by the top five states, labeled by state name, and all others labeled as "Other."

Run the query

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 = iff(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

The following example counts events from the StormEvents table based on a predefined list of interesting states. The interesting states are defined by the InterestingStates() function.

Run the query

StormEvents 
| where State in (InterestingStates()) 
| count

Output

Count
4775

The following query displays which states are considered interesting by the InterestingStates() function.

Run the query

.show function InterestingStates

Output

Name Parameters Body Folder DocString
InterestingStates () { dynamic(["WASHINGTON", "FLORIDA", "GEORGIA", "NEW YORK"]) }

Related content