---
title: Use ES|QL subqueries with IN and NOT IN
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. The subquery can appear in the...
url: https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/esql-in-subquery
products:
  - Elasticsearch
applies_to:
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Preview in 9.5
---

# Use ES|QL subqueries with IN and NOT IN
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/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-in-operator)
operators. The subquery can appear in the
[`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/where) command, the
[`EVAL`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/eval) command, and the
per-aggregate `WHERE` filter of
[`STATS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/stats-by) and
[`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/inlinestats-by)
<applies-to>Elastic Stack: Planned</applies-to>.
The subquery compares a field, expression, or tuple of expressions against the
set of values it returns. This lets you match 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. In `EVAL`, the same predicates produce a boolean column instead of
filtering rows.

## Syntax

```esql
... | WHERE <expression> IN (FROM index_pattern [| processing_commands]) | ...
... | WHERE (<e1>, <e2>[, ...]) IN (FROM index_pattern [| processing_commands]) | ...
... | EVAL <column> = <expression> IN (FROM index_pattern [| processing_commands]) | ...
... | STATS <agg> WHERE <expression> IN (FROM index_pattern [| processing_commands]) | ...
... | INLINE STATS <agg> WHERE <expression> IN (FROM index_pattern [| processing_commands]) | ...
```

`NOT IN` is supported in the same positions. The tuple form
<applies-to>Elastic Stack: Planned</applies-to> also works
in `EVAL` and in the per-aggregate `WHERE` of `STATS` and `INLINE STATS`.
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://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/from), and
[`ROW`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/row) and
[`TS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/ts) are also supported.
For a single-column `IN` subquery, the subquery must return exactly one column,
whose values are compared against the left-hand side of the `IN` subquery. For a
multi-column `IN` subquery, wrap two or more expressions in parentheses on the
left-hand side <applies-to>Elastic Stack: Planned</applies-to>. The subquery must then return the
same number of columns, which are compared **by position** against the tuple.
Each pair of columns must have compatible types. A single parenthesized field
such as `(emp_no) IN (...)` is still a single-column `IN` subquery, not a
multi-column `IN` subquery.
The outer query is not limited to `FROM` either: it can also start with `ROW` or
`TS` and still use an `IN` subquery.

## Description

An `IN` subquery 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://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/esql-from-subquery),
which contributes rows to the combined result set, an `IN` subquery returns
either one column or a tuple of columns. The outer `IN` or `NOT IN` predicate
uses those values 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`.
An `IN` subquery can also sit inside
[`CASE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case),
[`COALESCE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/coalesce),
[`IS NULL`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-is_null),
[`IS NOT NULL`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-is_not_null),
[`==`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-equals),
and [`!=`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-not_equals)
<applies-to>Elastic Stack: Planned</applies-to>.
For the full list of supported source and processing commands inside a subquery, refer to [ES|QL subqueries](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/esql-subquery).

## Examples

The following examples show how to use `IN` subqueries.

### 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://www.elastic.co/elastic/docs-builder/docs/4300/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://www.elastic.co/elastic/docs-builder/docs/4300/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://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/row),
[`FROM`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/from), and
[`TS`](https://www.elastic.co/elastic/docs-builder/docs/4300/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://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/esql-from-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://www.elastic.co/elastic/docs-builder/docs/4300/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.

### Compute a boolean column in EVAL

<applies-to>
  - Elastic Stack: Planned
</applies-to>

Use `EVAL` to store the `IN` result as a boolean column. Every input row is kept:
```esql
FROM employees
| EVAL m = emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)
| WHERE emp_no <= 10004
| SORT emp_no ASC
| KEEP emp_no, m
```


| emp_no:integer | m:boolean |
|----------------|-----------|
| 10001          | true      |
| 10002          | true      |
| 10003          | true      |
| 10004          | false     |

The first three employees match the subquery, so `m` is `true` for those rows and
`false` for `10004`.

### Filter aggregations in STATS and INLINE STATS

<applies-to>
  - Elastic Stack: Planned
</applies-to>

Use an `IN` subquery in the per-aggregate `WHERE` of
[`STATS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/stats-by) to include only
matching rows in that aggregation:
```esql
FROM employees
| STATS c = COUNT(*) WHERE emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)
```


| c:long |
|--------|
| 3      |

Multiple aggregations can each have their own `IN` filter, and you can mix them
with an unfiltered aggregation:
```esql
FROM employees
| STATS in_top3 = COUNT(*) WHERE emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no),
        not_in_top3 = COUNT(*) WHERE emp_no NOT IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no),
        total = COUNT(*)
```


| in_top3:long | not_in_top3:long | total:long |
|--------------|------------------|------------|
| 3            | 97               | 100        |

The same per-aggregate filter works with
[`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/inlinestats-by). The
count is appended to every input row:
```esql
FROM employees
| KEEP emp_no
| INLINE STATS c = COUNT(*) WHERE emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)
| WHERE emp_no <= 10005
| SORT emp_no
```


| emp_no:integer | c:long |
|----------------|--------|
| 10001          | 3      |
| 10002          | 3      |
| 10003          | 3      |
| 10004          | 3      |
| 10005          | 3      |

Three employees match the subquery, so `c` is `3` on every returned row.

### Use an `IN` subquery inside an expression

<applies-to>
  - Elastic Stack: Planned
</applies-to>

An `IN` subquery can be nested inside
[`CASE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case),
[`COALESCE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/coalesce),
[`IS NULL`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-is_null),
[`IS NOT NULL`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-is_not_null),
[`==`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-equals),
and [`!=`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-not_equals).
Use `CASE` to treat the subquery as a condition:
```esql
FROM employees
| WHERE CASE(emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no), true, false)
| SORT emp_no
| KEEP emp_no, first_name
```


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

In `EVAL`, `CASE` can map the same boolean result to another value:
```esql
FROM employees
| EVAL m = CASE(emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no), "yes", "no")
| WHERE emp_no <= 10004
| SORT emp_no ASC
| KEEP emp_no, m
```


| emp_no:integer | m:keyword |
|----------------|-----------|
| 10001          | yes       |
| 10002          | yes       |
| 10003          | yes       |
| 10004          | no        |

Use `COALESCE` to replace a possible `null` match with a default:
```esql
FROM employees
| WHERE COALESCE(emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no), false)
| SORT emp_no
| KEEP emp_no, first_name
```


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

Use `IS NULL` or `IS NOT NULL` to test whether the match itself is `null`. Here
every `emp_no` produces a definite `true` or `false`, so `IS NULL` matches no
rows:
```esql
FROM employees
| WHERE (emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)) IS NULL
| STATS c = COUNT(*)
```


| c:long |
|--------|
| 0      |

Compare the boolean result with `==` or `!=`. This example uses `==`:
```esql
FROM employees
| WHERE (emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)) == true
| SORT emp_no
| KEEP emp_no, first_name
```


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


### Match a tuple of values

<applies-to>
  - Elastic Stack: Planned
</applies-to>

Wrap two or more expressions in parentheses to compare a tuple against the
subquery. Columns are matched **by position**, not by name, so the subquery must
return the same number of columns in the same order, and each pair must have
compatible types:
```esql
FROM employees
| WHERE (emp_no, salary) IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no, salary)
| SORT emp_no
| KEEP emp_no, first_name, salary
```


| emp_no:integer | first_name:keyword | salary:integer |
|----------------|--------------------|----------------|
| 10001          | Georgi             | 57305          |
| 10002          | Bezalel            | 56371          |
| 10003          | Parto              | 61805          |

The subquery returns the `(emp_no, salary)` pairs of the first three employees,
and the outer query keeps only those exact pairs.
Use `NOT IN` to exclude matching tuples:
```esql
FROM employees
| WHERE (emp_no, salary) NOT IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no, salary)
| SORT emp_no
| KEEP emp_no, first_name
| LIMIT 3
```


| emp_no:integer | first_name:keyword |
|----------------|--------------------|
| 10004          | Chirstian          |
| 10005          | Kyoichi            |
| 10006          | Anneke             |

You can store a tuple match as a boolean column in `EVAL`:
```esql
FROM employees
| EVAL m = (emp_no, salary) IN (FROM employees
                                | SORT emp_no ASC
                                | LIMIT 3
                                | KEEP emp_no, salary)
| WHERE m
| SORT emp_no
| KEEP emp_no
```


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

Or use a tuple as a per-aggregate `STATS` filter:
```esql
FROM employees
| STATS c = COUNT(*) WHERE (emp_no, salary) IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no, salary)
```


| c:long |
|--------|
| 3      |

A tuple `IN` can also sit inside `CASE`, `COALESCE`, `IS [NOT] NULL`, `==`, and
`!=`:
```esql
FROM employees
| WHERE CASE((emp_no, salary) IN (FROM employees | WHERE emp_no == 10001 | KEEP emp_no, salary), true, false)
| SORT emp_no
| KEEP emp_no, salary
```


| emp_no:integer | salary:integer |
|----------------|----------------|
| 10001          | 57305          |

You can combine single-column and multi-column `IN` subqueries with `AND` and
`OR`.

## Limitations


#### Supported commands

An `IN` subquery can appear in the
[`WHERE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/where) command. It can also
appear in the [`EVAL`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/eval) command
and the per-aggregate `WHERE` filter of
[`STATS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/stats-by) and
[`INLINE STATS`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/inlinestats-by)
<applies-to>Elastic Stack: Planned</applies-to>.
It is not supported in the other commands such as [`SORT`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/sort),
[`LIMIT ... BY`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/limit), as a `STATS` or `INLINE STATS` grouping expression, or as an
argument of an aggregation function such as
`STATS c = SUM(CASE(x IN (...), 1, 0))`.

#### The subquery must return the expected number of columns

A single-column `IN` subquery must return exactly one column. A multi-column
`IN` subquery must return the same number of columns as the left-hand tuple
<applies-to>Elastic Stack: Planned</applies-to>.
A subquery that returns the wrong number of columns is rejected.

#### Supported expressions

An `IN` subquery can be combined with other predicates using `AND`, `OR`, and
`NOT`. It can also be nested inside
[`CASE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/case),
[`COALESCE`](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/conditional-functions-and-expressions/coalesce),
[`IS NULL`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-is_null),
[`IS NOT NULL`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-is_not_null),
[`==`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-equals),
and [`!=`](/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators#esql-not_equals)
<applies-to>Elastic Stack: Planned</applies-to>.
If the subquery sits inside `CASE`, `COALESCE`, or `IS [NOT] NULL`, that whole
expression can itself be nested in another expression.
Other functions, or a computed left-hand side such as `ABS(emp_no) IN (...)`
are not supported. In a `STATS` or `INLINE STATS` per-aggregate `WHERE`, the
left-hand side also cannot be a grouping alias.

#### Subqueries are non-correlated

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

## Related pages

- [ES|QL subqueries](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/esql-subquery): canonical definition and supported commands.
- [Use subqueries in a `FROM` command](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/esql-from-subquery): combine result sets from independently processed sources.
- [`WHERE` command](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/where): full reference for the `WHERE` command.
- [`EVAL` command](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/eval): compute a boolean column from an `IN` subquery.
- [`STATS` command](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/stats-by): per-aggregate `WHERE` filters, including `IN` subqueries.
- [`INLINE STATS` command](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/commands/inlinestats-by): per-aggregate `WHERE` filters that preserve input rows.
- [`IN` operator](https://www.elastic.co/elastic/docs-builder/docs/4300/reference/query-languages/esql/functions-operators/operators): the operator used to match against a list of literal values or a subquery.