!in operator
Applies to: ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
Filters a record set for data without a case-sensitive 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.
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 |
|---|
| 54291 |
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 |
| ... | ... |