!in~ operator

  • Article

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

The following table provides a comparison of the in operators:

Operator Description Case-Sensitive Example (yields true)
in Equals to one 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

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

When possible, use the case-sensitive !in.

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

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.

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.

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.

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.

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