!in~ operator
Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
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.
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.
StormEvents
| where State !in~ (dynamic(["Florida", "Georgia", "New York"]))
| count
Output
| Count |
|---|
| 54291 |
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
| 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.
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.
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 |
| ... | ... |