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 operators in the 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.

... | 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, and ROW and 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.

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, 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
• ROW
• TS

Processing commands:

• CHANGE_POINT
• COMPLETION
• DISSECT
• DROP
• ENRICH
• EVAL
• GROK
• INLINE STATS
• KEEP
• LIMIT
• LOOKUP JOIN
• MV_EXPAND
• RENAME
• RERANK
• SAMPLE
• SORT
• STATS
• WHERE

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

Use IN to keep only the rows whose value is contained in the subquery result:

FROM employees
| WHERE emp_no IN (FROM employees | WHERE salary > 70000 | KEEP emp_no)
| SORT emp_no
| KEEP emp_no
		

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

Use NOT IN to keep only the rows whose value is not contained in the subquery result:

FROM employees
| WHERE emp_no NOT IN (FROM employees | WHERE salary < 10000 | KEEP emp_no)
| SORT emp_no
| KEEP emp_no
| LIMIT 3
		

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.

Use STATS inside the subquery to compare against aggregated values:

FROM employees
| WHERE salary IN (FROM employees | STATS m = max(salary) by languages | KEEP m)
| KEEP emp_no, salary, languages
| SORT emp_no
		

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

Multiple IN subqueries can be combined with other predicates using AND, OR, and NOT:

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
		

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

Use a lookup join inside the subquery before building the list of values:

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
		

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.

The subquery can start with a ROW source command to match against an inline list of values:

FROM employees
| WHERE emp_no IN (ROW x = [10001, 10002, 10003] | MV_EXPAND x)
| SORT emp_no
| KEEP emp_no, first_name
		

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.

The outer query can also start with ROW, using an IN subquery to keep only the values that exist in another index:

ROW emp_no = [10001, 10002, 99999]
| MV_EXPAND emp_no
| WHERE emp_no IN (FROM employees | KEEP emp_no)
| SORT emp_no
		

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

The subquery can start with a 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:

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
		

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.

The outer query can also start with TS, so both the outer query and the subquery read from time series data:

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
		

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

A subquery can union several source commands with the FROM (...) syntax, mixing ROW, FROM, and TS branches. Each branch must produce the same single column:

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
		

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.

IN and NOT IN subqueries can be nested inside arbitrarily complex boolean conditions built from AND, OR, and NOT:

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
		

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.

An IN subquery can also appear inside a subquery in the FROM command. 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:

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
		

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.

IN subqueries can be used inside FORK branches, so each branch can apply its own subquery-based filter:

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
		

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.

An IN subquery can only appear in the WHERE command. It is not supported in other commands.

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.

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.

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

• Combine result sets with subqueries: use a subquery as a source in the FROM command.
• WHERE command: full reference for the WHERE command.
• IN operator: the operator used to match against a list of literal values or a subquery.
• Query multiple sources: high-level overview of combining data from multiple indices, clusters, subqueries, and views.