# LOOKUP-JOIN

The LOOKUP JOIN command adds new columns to your query results by finding documents in a lookup index that share the same join field value as your result rows. For each row 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, the output will contain one row for each matching combination. This command enables you to enrich your results with data from another index, simplifying data enrichment and analysis workflows.

## Syntax

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

### Parameters

#### <lookup_index>

The name of the lookup index. This must be a specific index name; wildcards, aliases, and remote cluster references are not supported. Indices used for lookups must be configured with the `lookup` index mode.

#### <join_condition>

Can be one of the following:
- A single field name.
- A comma-separated list of field names, for example `<field1>, <field2>, <field3>`.
- 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 (`==`, `>=`, `<=`, `>`, `<`, `!=`). Each field name in the join condition must exist in only one of the indexes. Use RENAME to resolve naming conflicts.
- An expression that includes full text 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.

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 contain multi-valued entries, those entries will not match anything (the added fields will contain null for those rows).

## Examples

Correlate source IPs with known malicious addresses by joining firewall logs with a threat list on the source IP field.

```esql
FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
```

Show only firewall logs where the source IP matches a known threat by filtering for rows with a non-null threat level after joining.

```esql
FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
| WHERE threat_level IS NOT NULL
```

Enrich system metrics with host inventory and ownership information by joining with both the host inventory and ownerships indices on the host name.

```esql
FROM system_metrics
| LOOKUP JOIN host_inventory ON host.name
| LOOKUP JOIN ownerships ON host.name
```

Add service ownership details to application logs by joining with the service owners index on the service ID.

```esql
FROM app_logs
| LOOKUP JOIN service_owners ON service_id
```

Filter employees by employee number before joining with language lookup data to demonstrate optimizer behavior with the WHERE clause before the LOOKUP JOIN.

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

Join employees with language lookup data, then filter by employee number to demonstrate optimizer behavior with the WHERE clause after the LOOKUP JOIN. The optimizer will move the filter before the join when possible.

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

## Limitations

- The lookup index must be a specific index name; wildcards, aliases, and remote cluster references are not supported.
- Indices used for lookups must be configured with the `lookup` index mode.
- If the join fields contain multi-valued entries, those entries will not match anything, and the added fields will contain null for those rows.