not-in-operator.md

title	description	ms.reviewer	ms.topic	ms.date
The case-insensitive !in~ string operator	Learn how to use the !in~ string operator to filter records for data without a case-insensitive string.	alexans	reference	03/29/2023

!in~ operator

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

[!INCLUDE in-operator-comparison]

Performance tips

[!INCLUDE performance-tip-note]

When possible, use the case-sensitive !in.

Syntax

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

[!INCLUDE syntax-conventions-note]

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 will consider up to 1,000,000 distinct values.

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.

[!div class="nextstepaction"] Run the query

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

Output

Count
54,291

Dynamic array

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

[!div class="nextstepaction"] Run the query

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

Output

Count
54291

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

[!div class="nextstepaction"] Run the query

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

Output

Count
54291

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.

[!div class="nextstepaction"] Run the query

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

Output

State	count_
KANSAS	3166
IOWA	2337
NEBRASKA	1766
OKLAHOMA	1716
SOUTH DAKOTA	1567
...	...

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.

[!div class="nextstepaction"] Run the query

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

Output

State	count_
KANSAS	3166
IOWA	2337
NEBRASKA	1766
OKLAHOMA	1716
SOUTH DAKOTA	1567
...	...