---
title: ES|QL LOOKUP JOIN command
description: 
url: https://www.elastic.co/docs/reference/query-languages/esql/commands/lookup-join
products:
  - Elasticsearch
---

# ES|QL LOOKUP JOIN command
<applies-to>
  - Elastic Cloud Serverless: Generally available
  - Elastic Stack: Generally available since 9.1
  - Elastic Stack: Preview in 9.0
</applies-to>

`LOOKUP JOIN` enables you to add data from another index, AKA a 'lookup'
index, to your ES|QL query results, simplifying data enrichment
and analysis workflows.
Refer to [the high-level landing page](https://www.elastic.co/docs/reference/query-languages/esql/esql-lookup-join) for an overview of the `LOOKUP JOIN` command, including use cases, prerequisites, and current limitations.

## Syntax

```esql
FROM <source_index>
| LOOKUP JOIN <lookup_index> ON <join_condition>
```


## Parameters

<definitions>
  <definition term="<lookup_index>">
    The name of the lookup index. This must be a specific index name or alias. Wildcards and remote cluster prefixes are not supported. If the query source includes remote indices, the lookup index must exist on all involved clusters. Indices used for lookups must be configured with the [`lookup` index mode](/docs/reference/elasticsearch/index-settings/index-modules#index-mode-setting).
  </definition>
  <definition term="<join_condition>">
    Can be one of the following:
    - A single field name
    - A comma-separated list of field names, for example `<field1>, <field2>, <field3>`  <applies-to>Elastic Stack: Generally available since 9.2</applies-to>
    - An expression with one or more predicates linked by `AND`, for example `<left_field1> >= <lookup_field1> AND <left_field2> == <lookup_field2>`. Each predicate compares a field from the left index with a field from the lookup index using [binary operators](/docs/reference/query-languages/esql/functions-operators/operators#esql-binary-operators) (`==`, `>=`, `<=`, `>`, `<`, `!=`). Each field name in the join condition must exist in only one of the indexes. Use RENAME to resolve naming conflicts. <applies-to>Elastic Stack: Preview since 9.2</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
    - An expression that includes [full text functions](https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/search-functions) and other Lucene-pushable functions, for example `MATCH(<lookup_field>, "search term") AND <left_field> == <lookup_field>`. These functions can be combined with binary operators and logical operators (`AND`, `OR`, `NOT`) to create complex join conditions. At least one condition that relates the lookup index fields to the left side of the join fields is still required. <applies-to>Elastic Stack: Preview since 9.3</applies-to> <applies-to>Elastic Cloud Serverless: Preview</applies-to>
  </definition>
  <definition term="If using join on a single field or a field list, the fields used must exist in both your current query results and in the lookup index. If the fields contains multi-valued entries, those entries will not match anything (the added fields will contain null for those rows).">
  </definition>
</definitions>


## Description

The `LOOKUP JOIN` command adds new columns to your ES|QL query
results table by finding documents in a lookup index that share the same
join field value as your result rows.
For each row in your results table that matches a document in the lookup
index based on the join fields, all fields from the matching document are
added as new columns to that row.
If multiple documents in the lookup index match a single row in your
results, the output will contain one row for each matching combination.
<tip>
  For important information about using `LOOKUP JOIN`, refer to [Usage notes](/docs/reference/query-languages/esql/esql-lookup-join#usage-notes).
</tip>


## Supported types


| field from the left index | field from the lookup index                                         |
|---------------------------|---------------------------------------------------------------------|
| boolean                   | boolean                                                             |
| byte                      | byte, short, integer, long, half_float, float, double, scaled_float |
| date                      | date                                                                |
| date_nanos                | date_nanos                                                          |
| double                    | half_float, float, double, scaled_float, byte, short, integer, long |
| float                     | half_float, float, double, scaled_float, byte, short, integer, long |
| half_float                | half_float, float, double, scaled_float, byte, short, integer, long |
| integer                   | byte, short, integer, long, half_float, float, double, scaled_float |
| ip                        | ip                                                                  |
| keyword                   | keyword                                                             |
| long                      | byte, short, integer, long, half_float, float, double, scaled_float |
| scaled_float              | half_float, float, double, scaled_float, byte, short, integer, long |
| short                     | byte, short, integer, long, half_float, float, double, scaled_float |
| text                      | keyword                                                             |


## Examples

The following examples show common `LOOKUP JOIN` use cases.

### IP threat correlation

Check whether source IPs match known malicious addresses:
```esql
FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
```

To filter only for those rows that have a matching `threat_list` entry, use `WHERE ... IS NOT NULL` with a field from the lookup index:
```esql
FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
| WHERE threat_level IS NOT NULL
```


### Host metadata correlation

Pull in environment or ownership details for each host to correlate with metrics data:
```esql
FROM system_metrics
| LOOKUP JOIN host_inventory ON host.name
| LOOKUP JOIN ownerships ON host.name
```


### Service ownership mapping

Show logs alongside the owning team or escalation information for faster triage:
```esql
FROM app_logs
| LOOKUP JOIN service_owners ON service_id
```


### Filter before LOOKUP JOIN

`LOOKUP JOIN` is generally faster when there are fewer rows to join with. ES|QL will try to perform any `WHERE` clause before the `LOOKUP JOIN` where possible. The following two queries produce the same results. One filters before the join, the other after. The optimizer will push the filter before the lookup when possible:
```esql
FROM employees
| EVAL language_code = languages
| WHERE emp_no >= 10091 AND emp_no < 10094
| LOOKUP JOIN languages_lookup ON language_code
```


| emp_no:integer | language_code:integer | language_name:keyword |
|----------------|-----------------------|-----------------------|
| 10091          | 3                     | Spanish               |
| 10092          | 1                     | English               |
| 10093          | 3                     | Spanish               |

```esql
FROM employees
| EVAL language_code = languages
| LOOKUP JOIN languages_lookup ON language_code
| WHERE emp_no >= 10091 AND emp_no < 10094
```


| emp_no:integer | language_code:integer | language_name:keyword |
|----------------|-----------------------|-----------------------|
| 10091          | 3                     | Spanish               |
| 10092          | 1                     | English               |
| 10093          | 3                     | Spanish               |