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 operators. The subquery can appear in the WHERE command, the EVAL command, and the per-aggregate WHERE filter of STATS and INLINE STATS .

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.

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

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, 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, COALESCE, IS NULL, IS NOT NULL, ==, and != .

For the full list of supported source and processing commands inside a subquery, refer to ES|QL subqueries.

The following examples show how to use IN subqueries.

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.

Use EVAL to store the IN result as a boolean column. Every input row is kept:

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
		

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

Use an IN subquery in the per-aggregate WHERE of STATS to include only matching rows in that aggregation:

FROM employees
| STATS c = COUNT(*) WHERE emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)
		

Multiple aggregations can each have their own IN filter, and you can mix them with an unfiltered aggregation:

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(*)
		

The same per-aggregate filter works with INLINE STATS. The count is appended to every input row:

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
		

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

An IN subquery can be nested inside CASE, COALESCE, IS NULL, IS NOT NULL, ==, and !=.

Use CASE to treat the subquery as a condition:

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
		

In EVAL, CASE can map the same boolean result to another value:

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
		

Use COALESCE to replace a possible null match with a default:

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
		

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:

FROM employees
| WHERE (emp_no IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no)) IS NULL
| STATS c = COUNT(*)
		

Compare the boolean result with == or !=. This example uses ==:

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
		

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:

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
		

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:

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
		

You can store a tuple match as a boolean column in EVAL:

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
		

Or use a tuple as a per-aggregate STATS filter:

FROM employees
| STATS c = COUNT(*) WHERE (emp_no, salary) IN (FROM employees | SORT emp_no ASC | LIMIT 3 | KEEP emp_no, salary)
		

A tuple IN can also sit inside CASE, COALESCE, IS [NOT] NULL, ==, and !=:

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
		

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

An IN subquery can appear in the WHERE command. It can also appear in the EVAL command and the per-aggregate WHERE filter of STATS and INLINE STATS .

It is not supported in the other commands such as SORT, LIMIT ... BY, 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)).

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 . A subquery that returns the wrong number of columns is rejected.

An IN subquery can be combined with other predicates using AND, OR, and NOT. It can also be nested inside CASE, COALESCE, IS NULL, IS NOT NULL, ==, and != . 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.

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

• ES|QL subqueries: canonical definition and supported commands.
• Use subqueries in a FROM command: combine result sets from independently processed sources.
• WHERE command: full reference for the WHERE command.
• EVAL command: compute a boolean column from an IN subquery.
• STATS command: per-aggregate WHERE filters, including IN subqueries.
• INLINE STATS command: per-aggregate WHERE filters that preserve input rows.
• IN operator: the operator used to match against a list of literal values or a subquery.