---
title: Filter rows with ES|QL IN subquery in a WHERE command
description: An ES|QL query wrapped in parentheses can be used as a subquery on the right-hand side of the IN and NOT IN operators in the WHERE command. The subquery...
url: https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/esql-in-subquery
products:
  - Elasticsearch
applies_to:
  - Elastic Cloud Serverless: Preview
  - Elastic Stack: Planned
---

# Filter rows with ES|QL IN subquery in a WHERE command
An ES|QL query wrapped in parentheses can be used as a subquery on the
right-hand side of the [`IN` and `NOT IN`](/elastic/elasticsearch/tree/main/reference/query-languages/esql/functions-operators/operators#esql-in-operator)
operators in the [`WHERE`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/where)
command.
The subquery filters rows from the outer query by comparing a field or
expression against the set of values it returns. This lets you filter against
the current results of another query without running it separately and copying
its values into a literal `IN` list.
Use `IN` to keep rows whose values match subquery results and `NOT IN` to
exclude them.

## Syntax

```esql
... | WHERE <expression> IN (FROM index_pattern [| processing_commands]) | ...
... | WHERE <expression> NOT IN (FROM index_pattern [| processing_commands]) | ...
```

The subquery starts with a source command followed by zero or more piped
processing commands, all enclosed in parentheses. The source command is usually
[`FROM`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/from), and
[`ROW`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/row) and
[`TS`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/ts) are also supported. The
subquery must return exactly one column, whose values are compared against
`<expression>`. The column and `<expression>` must have compatible types.
The outer query is not limited to `FROM` either: it can also start with `ROW` or
`TS` and still filter its rows with an `IN` subquery.

## Description

A subquery in a `WHERE` command is non-correlated: it runs independently and
cannot reference columns from the outer query. Because it runs at query time,
its results reflect the current state of the data.
Unlike a [subquery in a `FROM` command](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/esql-subquery),
which contributes rows to the combined result set, a subquery in a `WHERE`
command returns exactly one column. The outer `IN` or `NOT IN` predicate uses
the values from that column as its comparison set.
An `IN` subquery can itself contain another `IN` subquery, and multiple `IN`
subqueries can be combined with other predicates using `AND`, `OR`, and `NOT`.
The subquery pipeline can include commands such as the following:
Source commands:
- [`FROM`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/from)
- [`ROW`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/row)
- [`TS`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/ts)

Processing commands:
- [`CHANGE_POINT`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/change-point)
- [`COMPLETION`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/completion)
- [`DISSECT`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/dissect)
- [`DROP`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/drop)
- [`ENRICH`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/enrich)
- [`EVAL`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/eval)
- [`GROK`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/grok)
- [`INLINE STATS`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/inlinestats-by)
- [`KEEP`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/keep)
- [`LIMIT`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/limit)
- [`LOOKUP JOIN`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/lookup-join)
- [`MV_EXPAND`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/mv_expand)
- [`RENAME`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/rename)
- [`RERANK`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/rerank)
- [`SAMPLE`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/sample)
- [`SORT`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/sort)
- [`STATS`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/stats-by)
- [`WHERE`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/where)


## Examples

The following examples show how to use `IN` subqueries within the `WHERE` command.

### Filter by values from a subquery

Use `IN` to keep only the rows whose value is contained in the subquery result:
```esql
FROM employees
| WHERE emp_no IN (FROM employees | WHERE salary > 70000 | KEEP emp_no)
| SORT emp_no
| KEEP emp_no
```


| emp_no:integer |
|----------------|
| 10007          |
| 10019          |
| 10027          |
| 10029          |
| 10033          |
| 10045          |
| 10097          |
| 10099          |

The subquery selects the `emp_no` of every employee earning more than `70000`,
and the outer query keeps only those employees.

### Exclude values with NOT IN

Use `NOT IN` to keep only the rows whose value is *not* contained in the subquery
result:
```esql
FROM employees
| WHERE emp_no NOT IN (FROM employees | WHERE salary < 10000 | KEEP emp_no)
| SORT emp_no
| KEEP emp_no
| LIMIT 3
```


| emp_no:integer |
|----------------|
| 10001          |
| 10002          |
| 10003          |

Here the subquery selects every employee earning less than `10000`. No employee
matches, so the subquery returns an empty result. `NOT IN` therefore excludes
nothing and every employee is kept, so the first three rows by `emp_no` are
`10001` through `10003`.

### Aggregate data inside the subquery

Use `STATS` inside the subquery to compare against aggregated values:
```esql
FROM employees
| WHERE salary IN (FROM employees | STATS m = max(salary) by languages | KEEP m)
| KEEP emp_no, salary, languages
| SORT emp_no
```


| emp_no:integer | salary:integer | languages:integer |
|----------------|----------------|-------------------|
| 10007          | 74572          | 4                 |
| 10019          | 73717          | 1                 |
| 10029          | 74999          | null              |
| 10045          | 74970          | 3                 |
| 10094          | 66817          | 5                 |
| 10099          | 73578          | 2                 |

The subquery computes the maximum `salary` per language group, and the outer
query keeps only the employees whose `salary` matches one of those maximums.

### Combine multiple subqueries

Multiple `IN` subqueries can be combined with other predicates using `AND`, `OR`,
and `NOT`:
```esql
FROM employees
| WHERE emp_no NOT IN (FROM employees | WHERE languages IN (1, 2) | KEEP emp_no)
    AND emp_no IN (FROM employees | WHERE salary > 70000 | KEEP emp_no)
    AND languages > 0
| SORT emp_no
| KEEP emp_no, languages, salary
```


| emp_no:integer | languages:integer | salary:integer |
|----------------|-------------------|----------------|
| 10007          | 4                 | 74572          |
| 10045          | 3                 | 74970          |
| 10097          | 3                 | 71165          |

This query keeps employees who do not speak the two languages, earn more than
`70000`, and have a known number of languages.

### Use LOOKUP JOIN inside the subquery

Use a lookup join inside the subquery before building the list of values:
```esql
FROM employees
| WHERE emp_no IN (
    FROM employees
    | EVAL language_code = languages
    | LOOKUP JOIN languages_lookup ON language_code
    | WHERE language_name == "German"
    | KEEP emp_no
  )
| SORT emp_no
| KEEP emp_no, first_name
| LIMIT 5
```


| emp_no:integer | first_name:keyword |
|----------------|--------------------|
| 10003          | Parto              |
| 10007          | Tzvetan            |
| 10010          | Duangkaew          |
| 10031          | null               |
| 10036          | null               |

The subquery joins each employee's `languages` code with the `languages_lookup`
index, keeps only German speakers, and returns their `emp_no`. The outer query
then keeps only those employees.

### Use ROW as the subquery source

The subquery can start with a [`ROW`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/row)
source command to match against an inline list of values:
```esql
FROM employees
| WHERE emp_no IN (ROW x = [10001, 10002, 10003] | MV_EXPAND x)
| SORT emp_no
| KEEP emp_no, first_name
```


| emp_no:integer | first_name:keyword |
|----------------|--------------------|
| 10001          | Georgi             |
| 10002          | Bezalel            |
| 10003          | Parto              |

The `ROW` command builds a multivalued field, `MV_EXPAND` turns it into one value
per row, and the outer query keeps only the employees whose `emp_no` is one of
those values.

### Use ROW as the outer source

The outer query can also start with `ROW`, using an `IN` subquery to keep only the
values that exist in another index:
```esql
ROW emp_no = [10001, 10002, 99999]
| MV_EXPAND emp_no
| WHERE emp_no IN (FROM employees | KEEP emp_no)
| SORT emp_no
```


| emp_no:integer |
|----------------|
| 10001          |
| 10002          |

The `ROW` command provides a list of candidate `emp_no` values, and the subquery
filters out `99999`, which does not match any employee.

### Use TS as the subquery source

The subquery can start with a [`TS`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/ts)
source command to build the list of values from time series data. In this example
the outer query reads the downsampled `k8s-downsampled` index with `FROM`, while
the subquery uses `TS` over the raw `k8s` index:
```esql
FROM k8s-downsampled
| WHERE cluster IN (TS k8s
                    | STATS max_bytes = max(to_long(network.total_bytes_in)) BY cluster
                    | WHERE max_bytes > 10000
                    | KEEP cluster)
| STATS buckets = COUNT(*) BY cluster
| SORT cluster
```


| buckets:long | cluster:keyword |
|--------------|-----------------|
| 9            | prod            |
| 9            | qa              |

The subquery aggregates the raw time series metrics to find the clusters whose peak
ingest exceeds a threshold, and the outer query then reports the downsampled buckets
for those clusters. The outer `FROM` and the `TS` subquery reference different
indices because a single index cannot be read as both a standard source (`FROM`)
and a time series source (`TS`) in the same query.

### Use TS as the outer source

The outer query can also start with `TS`, so both the outer query and the subquery
read from time series data:
```esql
TS k8s
| WHERE cluster IN (TS k8s
                    | STATS max_bytes = max(to_long(network.total_bytes_in)) BY cluster
                    | WHERE max_bytes > 10000
                    | KEEP cluster)
| STATS max_bytes = max(to_long(network.total_bytes_in)) BY cluster
| SORT cluster
```


| max_bytes:long | cluster:keyword |
|----------------|-----------------|
| 10277          | prod            |
| 10797          | qa              |

The subquery keeps only the clusters whose maximum ingested bytes exceed `10000`,
and the outer query reports those clusters.

### Combine ROW, FROM, and TS sources in one subquery

A subquery can union several source commands with the `FROM (...)` syntax, mixing
[`ROW`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/row),
[`FROM`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/from), and
[`TS`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/ts) branches. Each branch must
produce the same single column:
```esql
FROM k8s-downsampled
| WHERE cluster IN (FROM
                       (ROW cluster = "staging"),
                       (FROM employees | WHERE emp_no == 10001 | EVAL cluster = "prod" | KEEP cluster),
                       (TS k8s
                        | STATS max_bytes = max(to_long(network.total_bytes_in)) BY cluster
                        | WHERE max_bytes > 10500
                        | KEEP cluster)
                   )
| STATS buckets = COUNT(*) BY cluster
| SORT cluster
```


| buckets:long | cluster:keyword |
|--------------|-----------------|
| 9            | prod            |
| 9            | qa              |
| 9            | staging         |

The `ROW` branch contributes `staging`, the `FROM` branch contributes `prod`, and
the `TS` branch contributes `qa`. Their union is the list of values the outer query
matches against, so all three clusters are kept.

### Nest `IN` subqueries in complex conditions

`IN` and `NOT IN` subqueries can be nested inside arbitrarily complex boolean
conditions built from `AND`, `OR`, and `NOT`:
```esql
FROM employees
| WHERE emp_no IN (FROM employees | WHERE salary > 73000 | KEEP emp_no)
     OR (gender == "F" AND (salary > 70000 OR emp_no NOT IN (FROM employees | WHERE languages > 1 | KEEP emp_no)))
| SORT emp_no
| KEEP emp_no, gender, languages, salary
| LIMIT 15
```


| emp_no:integer | gender:keyword | languages:integer | salary:integer |
|----------------|----------------|-------------------|----------------|
| 10007          | F              | 4                 | 74572          |
| 10009          | F              | 1                 | 66174          |
| 10019          | null           | 1                 | 73717          |
| 10023          | F              | null              | 47896          |
| 10024          | F              | null              | 64675          |
| 10027          | F              | null              | 73851          |
| 10029          | M              | null              | 74999          |
| 10041          | F              | 1                 | 56415          |
| 10044          | F              | 1                 | 39728          |
| 10045          | M              | 3                 | 74970          |
| 10092          | F              | 1                 | 25976          |
| 10099          | F              | 2                 | 73578          |

The outer query keeps an employee when either their `emp_no` is returned by the
first `IN` subquery (salaries above `73000`), or they are female and either earn more
than `70000` or their `emp_no` is *not* returned by the `NOT IN` subquery.

### Use an `IN` subquery inside a FROM subquery

An `IN` subquery can also appear inside a [subquery in the `FROM` command](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/esql-subquery).
Here the `FROM` command unions two employee sources, and the second branch is
filtered with an `IN` subquery that itself nests a `NOT IN` subquery:
```esql
FROM
  (FROM employees | WHERE salary > 70000 | KEEP emp_no, first_name, salary, languages),
  (FROM employees
   | WHERE emp_no IN (
       FROM employees
       | WHERE salary NOT IN (
           FROM employees | SORT salary DESC | LIMIT 3 | KEEP salary
         )
       | KEEP emp_no)
   | WHERE languages == 1
   | KEEP emp_no, first_name, salary, languages)
| SORT emp_no
| KEEP emp_no, first_name, salary, languages
| LIMIT 6
```


| emp_no:integer | first_name:keyword | salary:integer | languages:integer |
|----------------|--------------------|----------------|-------------------|
| 10005          | Kyoichi            | 63528          | 1                 |
| 10007          | Tzvetan            | 74572          | 4                 |
| 10009          | Sumant             | 66174          | 1                 |
| 10013          | Eberhardt          | 48735          | 1                 |
| 10019          | Lillian            | 73717          | 1                 |
| 10019          | Lillian            | 73717          | 1                 |

The first `FROM` branch keeps every employee earning more than `70000`. The second
branch keeps employees who speak language one and whose `emp_no` is returned by
the `IN` subquery, which in turn excludes the three highest salaries with a nested
`NOT IN` subquery. The outer query reports the union of both branches.

### Combine an `IN` subquery with `FORK`

`IN` subqueries can be used inside [`FORK`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/fork)
branches, so each branch can apply its own subquery-based filter:
```esql
FROM employees
| FORK (WHERE emp_no IN (FROM employees | WHERE salary > 74000 | KEEP emp_no) | KEEP emp_no, salary)
       (WHERE emp_no IN (FROM employees | WHERE salary < 30000 | KEEP emp_no) | KEEP emp_no, salary)
| SORT emp_no, _fork
| KEEP emp_no, salary, _fork
| LIMIT 5
```


| emp_no:integer | salary:integer | _fork:keyword |
|----------------|----------------|---------------|
| 10007          | 74572          | fork1         |
| 10015          | 25324          | fork2         |
| 10026          | 28336          | fork2         |
| 10029          | 74999          | fork1         |
| 10035          | 25945          | fork2         |

The first `FORK` branch keeps the high earners returned by its `IN` subquery, the
second branch keeps the low earners returned by its `IN` subquery, and the `_fork`
column records which branch produced each row.

## Limitations


#### `IN` subqueries are only supported in the WHERE command

An `IN` subquery can only appear in the [`WHERE`](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/where)
command. It is not supported in other commands.

#### The subquery must return exactly one column

The subquery must produce a single column to compare against the left-hand side
expression. A subquery that returns zero or more than one column is rejected.

#### The `IN` subquery must be a top-level predicate

An `IN` subquery must sit at the top of the `WHERE` condition, optionally combined
with other predicates using `AND`, `OR`, and `NOT`. It cannot be used as an
argument to another expression, such as inside a scalar function or an
`IS NOT NULL` check.

#### Subqueries are non-correlated

The subquery is executed independently and cannot reference columns from the
outer query.

## Related pages

- [Combine result sets with subqueries](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/esql-subquery): use a subquery as a source in the `FROM` command.
- [`WHERE` command](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/commands/where): full reference for the `WHERE` command.
- [`IN` operator](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/functions-operators/operators): the operator used to match against a list of literal values or a subquery.
- [Query multiple sources](https://docs-v3-preview.elastic.dev/elastic/elasticsearch/tree/main/reference/query-languages/esql/esql-multi): high-level overview of combining data from multiple indices, clusters, subqueries, and views.