---
title: Use ES|QL subqueries in a FROM command
description: A subquery can be used in place of an index pattern in the FROM command. Each subquery is executed independently. The final output combines all these...
url: https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-from-subquery
products:
  - Elasticsearch
applies_to:
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.5, Preview in 9.4
---

# Use ES|QL subqueries in a FROM command
A [subquery](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-subquery) can be used
in place of an index pattern in the [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/commands/from) command.
Each subquery is executed independently. The final output combines all these
results into a single list, including any duplicate rows.

## Syntax

```esql
FROM index_pattern [, subquery]* [METADATA fields] 
FROM subquery [, subquery]* [METADATA fields] 
```

Multiple subqueries and regular index patterns can be combined in a single
`FROM` clause, separated by commas.

## Description

Much like [views](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-views),
subqueries in a `FROM` command enable you to combine results from multiple independently processed
data sources within a single query. Each subquery runs its own pipeline of
processing commands (such as `WHERE`, `EVAL`, `STATS`, or `SORT`) and the
results are combined together with results from other index patterns, views or subqueries
in the `FROM` clause.
Fields that exist in one source but not another are filled with `null` values.
The [`METADATA` directive](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-metadata-fields)
is also supported on either the subquery or the outer `FROM`.

## Examples

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

### Combine data from multiple indices

Use a subquery alongside a regular index pattern to combine results from
different indices:
```esql
FROM employees,
     (FROM sample_data)
| WHERE (emp_no >= 10091 AND emp_no < 10094)  OR emp_no IS NULL
| SORT emp_no, client_ip
| KEEP emp_no, languages, client_ip
```


| emp_no:integer | languages:integer | client_ip:ip |
|----------------|-------------------|--------------|
| 10091          | 3                 | null         |
| 10092          | 1                 | null         |
| 10093          | 3                 | null         |
| null           | null              | 172.21.0.5   |
| null           | null              | 172.21.2.113 |
| null           | null              | 172.21.2.162 |
| null           | null              | 172.21.3.15  |
| null           | null              | 172.21.3.15  |
| null           | null              | 172.21.3.15  |
| null           | null              | 172.21.3.15  |

Rows from `employees` have `null` for `client_ip`, while rows from `sample_data`
have `null` for `emp_no` and `languages`, because each index has different fields.

### Use only subqueries (no main index pattern)

You can use one or more subqueries without specifying a regular index pattern:
```esql
FROM (FROM employees)
| WHERE emp_no >= 10091 AND emp_no < 10094
| SORT emp_no
| KEEP emp_no, languages
```


| emp_no:integer | languages:integer |
|----------------|-------------------|
| 10091          | 3                 |
| 10092          | 1                 |
| 10093          | 3                 |

The `FROM` clause contains only a subquery with no regular index pattern. The
subquery wraps the `employees` index, and the outer query filters, sorts, and
projects the results.

### Filter data inside a subquery

Apply a `WHERE` clause inside the subquery to pre-filter data before combining:
```esql
FROM employees,
     (FROM sample_data metadata _index
      | WHERE client_ip == "172.21.3.15")
     metadata _index
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| EVAL _index = MV_LAST(SPLIT(_index, ":"))
| SORT emp_no
| KEEP _index, emp_no, languages, client_ip
```


| _index:keyword | emp_no:integer | languages:integer | client_ip:ip |
|----------------|----------------|-------------------|--------------|
| employees      | 10091          | 3                 | null         |
| employees      | 10092          | 1                 | null         |
| employees      | 10093          | 3                 | null         |
| sample_data    | null           | null              | 172.21.3.15  |
| sample_data    | null           | null              | 172.21.3.15  |
| sample_data    | null           | null              | 172.21.3.15  |
| sample_data    | null           | null              | 172.21.3.15  |

The `WHERE` inside the subquery filters `sample_data` to only rows where
`client_ip` is `172.21.3.15` before combining with `employees`. The `_index`
metadata field shows which index each row originated from.

### Aggregate data inside a subquery

Use `STATS` inside a subquery to aggregate data before combining with other sources:
```esql
FROM employees,
     (FROM sample_data metadata _index
      | STATS cnt = count(*) by _index, client_ip)
     metadata _index
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| EVAL _index = MV_LAST(SPLIT(_index, ":"))
| SORT _index, emp_no, client_ip
| KEEP _index, emp_no, languages, cnt, client_ip
```


| _index:keyword | emp_no:integer | languages:integer | cnt:long | client_ip:ip |
|----------------|----------------|-------------------|----------|--------------|
| employees      | 10091          | 3                 | null     | null         |
| employees      | 10092          | 1                 | null     | null         |
| employees      | 10093          | 3                 | null     | null         |
| sample_data    | null           | null              | 1        | 172.21.0.5   |
| sample_data    | null           | null              | 1        | 172.21.2.113 |
| sample_data    | null           | null              | 1        | 172.21.2.162 |
| sample_data    | null           | null              | 4        | 172.21.3.15  |

The `STATS` inside the subquery aggregates `sample_data` by counting rows per
`client_ip` before combining with `employees`. The `cnt` column is `null` for
`employees` rows since that field only exists in the subquery output.

### Combine multiple subqueries

Multiple subqueries can be combined in a single `FROM` clause:
```esql
FROM employees,
     (FROM sample_data metadata _index
      | STATS cnt = count(*) by _index, client_ip),
     (FROM sample_data_str metadata _index
      | STATS cnt = count(*) by _index, client_ip)
     metadata _index
| EVAL client_ip = client_ip::ip, _index = MV_LAST(SPLIT(_index, ":"))
| WHERE client_ip == "172.21.3.15" AND cnt >0
| SORT _index, emp_no, client_ip
| KEEP _index, emp_no, languages, cnt, client_ip
```


| _index:keyword  | emp_no:integer | languages:integer | cnt:long | client_ip:ip |
|-----------------|----------------|-------------------|----------|--------------|
| sample_data     | null           | null              | 4        | 172.21.3.15  |
| sample_data_str | null           | null              | 4        | 172.21.3.15  |

Two subqueries aggregate `sample_data` and `sample_data_str` separately, each
counting rows by `client_ip`. The results are combined and then filtered to only
show rows where `client_ip` is `172.21.3.15`. The `_index` field confirms each
row's source.

### Use LOOKUP JOIN inside a subquery

Enrich subquery results with a lookup join before combining:
```esql
FROM employees,
     (FROM sample_data
      | EVAL client_ip = client_ip::keyword
      | LOOKUP JOIN clientips_lookup ON client_ip)
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| SORT emp_no, client_ip
| KEEP emp_no, languages, client_ip, env
```


| emp_no:integer | languages:integer | client_ip:keyword | env:keyword |
|----------------|-------------------|-------------------|-------------|
| 10091          | 3                 | null              | null        |
| 10092          | 1                 | null              | null        |
| 10093          | 3                 | null              | null        |
| null           | null              | 172.21.0.5        | Development |
| null           | null              | 172.21.2.113      | QA          |
| null           | null              | 172.21.2.162      | QA          |
| null           | null              | 172.21.3.15       | Production  |
| null           | null              | 172.21.3.15       | Production  |
| null           | null              | 172.21.3.15       | Production  |
| null           | null              | 172.21.3.15       | Production  |

The `LOOKUP JOIN` inside the subquery joins each `sample_data` row with the
`env` field from `clientips_lookup` based on `client_ip`. The `env` column is
`null` for `employees` rows since the lookup only applies within the subquery.

### Sort and limit inside a subquery

Use `SORT` and `LIMIT` inside a subquery to return only top results:
```esql
FROM employees,
     (FROM sample_data
      | STATS cnt = count(*) by client_ip
      | SORT cnt DESC
      | LIMIT 1)
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no IS NULL
| SORT emp_no, client_ip
| KEEP emp_no, languages, cnt, client_ip
```


| emp_no:integer | languages:integer | cnt:long | client_ip:ip |
|----------------|-------------------|----------|--------------|
| 10091          | 3                 | null     | null         |
| 10092          | 1                 | null     | null         |
| 10093          | 3                 | null     | null         |
| null           | null              | 4        | 172.21.3.15  |

The subquery aggregates `sample_data` by `client_ip`, sorts by count in
descending order, and limits to the top result. Only the `client_ip` with the
highest count (`172.21.3.15` with 4 occurrences) is included when combined with
`employees`.

### Combine time series data with a TS subquery

<applies-to>
  - Elastic Stack: Generally available since 9.5
</applies-to>

Use a [`TS`](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/commands/ts) subquery to read
from a time series index and combine the results with a regular index:
```esql
FROM sample_data,
     (TS k8s
      | STATS max_rate=max(rate(network.total_bytes_in)) BY cluster
      | EVAL max_rate=ROUND(max_rate, 4))
| EVAL has_ts = max_rate IS NOT NULL, rate_per_minute = max_rate * 60
| WHERE rate_per_minute IS NOT NULL OR client_ip == "172.21.3.15"
| SORT has_ts, cluster, @timestamp
| KEEP @timestamp, client_ip, cluster, max_rate, rate_per_minute
```


| @timestamp:datetime      | client_ip:ip | cluster:keyword | max_rate:double | rate_per_minute:double |
|--------------------------|--------------|-----------------|-----------------|------------------------|
| 2023-10-23T13:51:54.732Z | 172.21.3.15  | null            | null            | null                   |
| 2023-10-23T13:52:55.015Z | 172.21.3.15  | null            | null            | null                   |
| 2023-10-23T13:53:55.832Z | 172.21.3.15  | null            | null            | null                   |
| 2023-10-23T13:55:01.543Z | 172.21.3.15  | null            | null            | null                   |
| null                     | null         | prod            | 8.7167          | 523.002                |
| null                     | null         | qa              | 13.1737         | 790.422                |
| null                     | null         | staging         | 7.3776          | 442.656                |

The `TS` subquery reads the `k8s` time series index and uses the
[`RATE`](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/functions-operators/time-series-aggregation-functions/rate)
function to compute the maximum per-second rate of the `network.total_bytes_in`
counter per `cluster`. The outer query combines these aggregates with
`sample_data`, derives `rate_per_minute` from `max_rate`, and keeps the time
series rows plus the `sample_data` rows for client IP `172.21.3.15`. Because each
branch exposes different fields, `sample_data` rows have `null` for `cluster`,
`max_rate`, and `rate_per_minute`, while the time series rows have `null` for
`client_ip`.

### Use only TS subqueries

<applies-to>
  - Elastic Stack: Generally available since 9.5
</applies-to>

Combine several `TS` subqueries in a single `FROM` clause, without a regular
index pattern:
```esql
FROM
    (TS k8s | STATS max_bytes=max(to_long(network.total_bytes_in)) BY cluster | KEEP cluster, max_bytes),
    (TS k8s-downsampled | STATS max_eth0_tx = max(network.eth0.tx) BY pod | KEEP pod, max_eth0_tx)
| SORT cluster, pod
| KEEP cluster, max_bytes, pod, max_eth0_tx
```


| cluster:keyword | max_bytes:long | pod:keyword | max_eth0_tx:double |
|-----------------|----------------|-------------|--------------------|
| prod            | 10277          | null        | null               |
| qa              | 10797          | null        | null               |
| staging         | 7403           | null        | null               |
| null            | null           | one         | 998.0              |
| null            | null           | three       | 734.0              |
| null            | null           | two         | 576.0              |

Each `TS` subquery aggregates a different time series index (`k8s` by `cluster`
and `k8s-downsampled` by `pod`). Because the two branches expose different
fields, each row carries `null` for the columns that come from the other
subquery.

### Inline dataset with a ROW subquery

<applies-to>
  - Elastic Stack: Generally available since 9.5
</applies-to>

Use a [`ROW`](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/commands/row) subquery to
introduce rows with literal values alongside data read from an index:
```esql
FROM
    employees,
    (ROW emp_no = 99999, languages = 99)
| WHERE (emp_no >= 10091 AND emp_no < 10094) OR emp_no == 99999
| SORT emp_no
| KEEP emp_no, languages
```


| emp_no:integer | languages:integer |
|----------------|-------------------|
| 10091          | 3                 |
| 10092          | 1                 |
| 10093          | 3                 |
| 99999          | 99                |

The `ROW` subquery contributes a single synthesized `(emp_no, languages)` row
that is combined with the matching rows from `employees`.

### Use only ROW subqueries

<applies-to>
  - Elastic Stack: Generally available since 9.5
</applies-to>

Multiple `ROW` subqueries can be combined without a regular index pattern:
```esql
FROM
    (ROW emp_no = 1, languages = 5),
    (ROW emp_no = 2, languages = 10),
    (ROW emp_no = 3, languages = 15)
| SORT emp_no
| KEEP emp_no, languages
```


| emp_no:integer | languages:integer |
|----------------|-------------------|
| 1              | 5                 |
| 2              | 10                |
| 3              | 15                |

Each `ROW` subquery contributes one row, and the outer query sorts and projects
the combined results.

### Combine FROM, TS, and ROW subqueries

<applies-to>
  - Elastic Stack: Generally available since 9.5
</applies-to>

Different source commands can be mixed in a single `FROM` clause. This example
combines a regular index pattern with a `FROM` subquery, a `TS` subquery, and a
`ROW` subquery:
```esql
FROM sample_data,
     (FROM sample_data | WHERE client_ip == "172.21.0.5"),
     (TS k8s
      | STATS max_bytes=max(to_long(network.total_bytes_in)) BY cluster
      | SORT cluster),
     (ROW cluster = "synthetic", max_bytes = to_long(99999))
| WHERE max_bytes IS NOT NULL OR client_ip == "172.21.0.5"
| SORT max_bytes, cluster, @timestamp
| KEEP @timestamp, client_ip, cluster, max_bytes
```


| @timestamp:datetime      | client_ip:ip | cluster:keyword | max_bytes:long |
|--------------------------|--------------|-----------------|----------------|
| null                     | null         | staging         | 7403           |
| null                     | null         | prod            | 10277          |
| null                     | null         | qa              | 10797          |
| null                     | null         | synthetic       | 99999          |
| 2023-10-23T13:33:34.937Z | 172.21.0.5   | null            | null           |
| 2023-10-23T13:33:34.937Z | 172.21.0.5   | null            | null           |

Each branch contributes its own rows to the combined result: `sample_data` and
the `FROM` subquery provide the `client_ip` rows, the `TS` subquery provides the
per-`cluster` aggregates, and the `ROW` subquery provides the synthesized
`synthetic` row. Fields that don't exist in a given branch are filled with
`null`.

### Subqueries with METADATA

The [`METADATA` directive](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-metadata-fields) is supported both inside and outside a subquery.
If the directive is used only outside the subquery, it will report `null` for the values within the subquery:
```esql
FROM employees,
     (FROM sample_data)
         METADATA _index, _index_mode
| WHERE emp_no == 10091 OR emp_no IS NULL
| STATS count=COUNT(*) BY emp_no, _index, _index_mode
```


| count:long | emp_no:integer | _index:keyword | _index_mode:keyword |
|------------|----------------|----------------|---------------------|
| 1          | 10091          | employees      | standard            |
| 7          | null           | null           | null                |

To see the combined values from within the subquery include the directive inside as well:
```esql
FROM employees,
     (FROM sample_data METADATA _index, _index_mode)
         METADATA _index, _index_mode
| WHERE emp_no == 10091 OR emp_no IS NULL
| STATS count=COUNT(*) BY emp_no, _index, _index_mode
```


| count:long | emp_no:integer | _index:keyword | _index_mode:keyword |
|------------|----------------|----------------|---------------------|
| 1          | 10091          | employees      | standard            |
| 7          | null           | sample_data    | standard            |

If you only have the directive within the subquery, null values will be returned for the indices outside the subquery:
```esql
FROM employees,
     (FROM sample_data METADATA _index, _index_mode)
| WHERE emp_no == 10091 OR emp_no IS NULL
| STATS count=COUNT(*) BY emp_no, _index, _index_mode
```


| count:long | emp_no:integer | _index:keyword | _index_mode:keyword |
|------------|----------------|----------------|---------------------|
| 1          | 10091          | null           | null                |
| 7          | null           | sample_data    | standard            |


## Limitations


#### Nested FROM subqueries are not supported

A `FROM` subquery cannot contain another `FROM` subquery. Only one
level of `FROM` nesting is allowed. However, a `FROM` subquery can contain
[`IN` subqueries](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-in-subquery) in its
`WHERE` commands.
For example, this query is **not supported** because the inner `FROM` itself
contains subqueries:
```esql
FROM
    (FROM
        (FROM employees | WHERE emp_no > 10090),
        (FROM sample_data | WHERE client_ip == "172.21.3.15")
    ),
    (FROM
        (FROM employees | WHERE emp_no < 10010),
        (FROM sample_data | WHERE client_ip == "172.21.0.5")
    )
| KEEP emp_no, languages, client_ip
```

Instead, use only a single level of subqueries:
```esql
FROM
    (FROM employees | WHERE emp_no > 10090),
    (FROM sample_data | WHERE client_ip == "172.21.3.15"),
    (FROM employees | WHERE emp_no < 10010),
    (FROM sample_data | WHERE client_ip == "172.21.0.5")
| KEEP emp_no, languages, client_ip
```


#### FORK is not supported with FROM subqueries

The [`FORK`](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/commands/fork) command cannot
be used inside a `FROM` subquery or after a `FROM` that contains subqueries.
For example, using `FORK` **inside** a subquery is not supported:
```esql
FROM
    (FROM employees
     | FORK
         (WHERE emp_no > 10090)
         (WHERE emp_no < 10010))
| KEEP emp_no, languages
```

And using `FORK` **after** a `FROM` with subqueries is also not supported:
```esql
FROM
    employees,
    (FROM sample_data | WHERE client_ip == "172.21.3.15")
| FORK
    (WHERE emp_no > 10090)
    (WHERE client_ip IS NOT NULL)
```


## Related pages

- [ES|QL subqueries](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-subquery): canonical definition, supported commands, and links to both subquery usage contexts.
- [Use subqueries in a `WHERE` command](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-in-subquery): filter rows using subqueries with `IN` and `NOT IN`.
- [Define virtual indices using ES|QL views](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/esql-views): the closest alternative to subqueries in `FROM`, with a persisted, named definition.
- [`FROM` command](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/commands/from): full reference for index expressions.
- [`FORK` command](https://www.elastic.co/elastic/docs-builder/docs/3802/reference/query-languages/esql/commands/fork): the other branching construct in ES|QL, which shares the same branching limits.