in operators

• 1: The case-insensitive !in~ string operator
• 2: The case-insensitive in~ string operator
• 3: The case-sensitive !in string operator
• 4: The case-sensitive in string operator

1 - The case-insensitive !in~ string operator

Filters a record set for data without a case-insensitive string.

Performance tips

When possible, use the case-sensitive !in.

Syntax

T | where col !in~ (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

List of scalars

The following query shows how to use !in~ with a comma-separated list of scalar values.

StormEvents 
| where State !in~ ("Florida", "Georgia", "New York") 
| count

Output

Dynamic array

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

StormEvents 
| where State !in~ (dynamic(["Florida", "Georgia", "New York"])) 
| count

Output

The same query can also be written with a let statement.

let states = dynamic(["Florida", "Georgia", "New York"]);
StormEvents 
| where State !in~ (states)
| summarize count() by State

Output

Tabular expression

The following query shows how to use !in~ with an inline tabular expression. Notice that an inline tabular expression must be enclosed with double parentheses.

StormEvents 
| where State !in~ (PopulationData | where Population > 5000000 | project State)
| summarize count() by State

Output

The same query can also be written with a let statement. Notice that the double parentheses as provided in the last example aren’t necessary in this case.

let large_states = PopulationData | where Population > 5000000 | project State;
StormEvents 
| where State !in~ (large_states)
| summarize count() by State

Output

2 - The case-insensitive in~ string operator

Filters a record set for data with a case-insensitive string.

Performance tips

When possible, use the case-sensitive in.

Syntax

T | where col in~ (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Examples

List of scalars

The following query shows how to use in~ with a comma-separated list of scalar values.

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

Output

Dynamic array

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

StormEvents 
| where State in~ (dynamic(["FLORIDA", "georgia", "NEW YORK"])) 
| count

Output

The same query can also be written with a let statement.

let states = dynamic(["FLORIDA", "georgia", "NEW YORK"]);
StormEvents 
| where State has_any (states)
| summarize count() by State

Output

Tabular expression

The following query shows how to use in~ with an inline tabular expression. Notice that an inline tabular expression must be enclosed with double parentheses.

StormEvents 
| where State in~ (PopulationData | where Population > 5000000 | project State)
| summarize count() by State

Output

The same query can also be written with a let statement. Notice that the double parentheses as provided in the last example aren’t necessary in this case.

let large_states = PopulationData | where Population > 5000000 | project State;
StormEvents 
| where State in~ (large_states)
| summarize count() by State

Output

3 - The case-sensitive !in string operator

Filters a record set for data without a case-sensitive string.

Performance tips

Syntax

T | where col !in (expression, … )

Parameters

Returns

Rows in T for which the predicate is true.

Example

List of scalars

The following query shows how to use !in with a comma-separated list of scalar values.

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

Output

Dynamic array

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

StormEvents 
| where State !in (dynamic(["FLORIDA", "GEORGIA", "NEW YORK"])) 
| count

Output

The same query can also be written with a let statement.

let states = dynamic(["FLORIDA", "GEORGIA", "NEW YORK"]);
StormEvents 
| where State !in (states)
| summarize count() by State

Output

Tabular expression

The following query shows how to use !in with an inline tabular expression. Notice that an inline tabular expression must be enclosed with double parentheses.

StormEvents 
| where State !in (PopulationData | where Population > 5000000 | project State)
| summarize count() by State

Output

The same query can also be written with a let statement. Notice that the double parentheses as provided in the last example aren’t necessary in this case.

let large_states = PopulationData | where Population > 5000000 | project State;
StormEvents 
| where State !in (large_states)
| summarize count() by State

Output

4 - The case-sensitive in string operator

Filters a record set for data with a case-sensitive string.

Performance tips

Syntax

T | where col in (expression, … )

Parameters

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.

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

Output

Dynamic array

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

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

Output

Tabular expression

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

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.

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

Output

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.”

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

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.

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

Output

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

.show function InterestingStates

Output

Related content

• in~ operator
• !in~ operator
• !in operator
• contains_cs operator
• has_cs operator