elasticsearch/docs/reference/esql/processing-commands/lookup.asciidoc

[discrete]
[[esql-lookup-join]]
=== `LOOKUP JOIN`

[WARNING]
==== 
This functionality is in technical preview and may be
changed or removed in a future release. Elastic will work to fix any
issues, but features in technical preview are not subject to the support
SLA of official GA features. ::::
====
`LOOKUP JOIN` enables you to add data from another index, AKA a 'lookup'
index, to your ++{{++esql}} query results, simplifying data enrichment
and analysis workflows.

*Syntax*

....
FROM <source_index>
| LOOKUP JOIN <lookup_index> ON <field_name>
....

[source,esql]
----
FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
| WHERE threat_level IS NOT NULL
----

*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.

`field_name`:: 
The field to join on. This field must exist
in both your current query results and in the lookup index. If the field
contains multi-valued entries, those entries will not match anything
(the added fields will contain `null` for those rows).

*Description*

The `LOOKUP JOIN` command adds new columns to your ++{++esql} 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 field, 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.

*Examples*

[TIP]
====
In case of name collisions, the newly created columns will override existing columns.
====

*IP Threat correlation*: This query would allow you to see if any source
IPs match known malicious addresses.

[source,esql]
----
FROM firewall_logs
| LOOKUP JOIN threat_list ON source.IP
----

*Host metadata correlation*: This query pulls in environment or
ownership details for each host to correlate with your metrics data.

[source,esql]
----
FROM system_metrics
| LOOKUP JOIN host_inventory ON host.name
| LOOKUP JOIN employees ON host.name
----

*Service ownership mapping*: This query would show logs with the owning
team or escalation information for faster triage and incident response.

[source,esql]
----
FROM app_logs
| LOOKUP JOIN service_owners ON service_id
----

`LOOKUP JOIN` is generally faster when there are fewer rows to join
with. {esql} will try and perform any `WHERE` clause before the
`LOOKUP JOIN` where possible.

The two following examples will have the same results. The two examples
have the `WHERE` clause before and after the `LOOKUP JOIN`. It does not
matter how you write your query, our optimizer will move the filter
before the lookup when ran.

[source,esql]
----
FROM Left
| WHERE Language IS NOT NULL
| LOOKUP JOIN Right ON Key
----

[source,esql]
----
FROM Left
| LOOKUP JOIN Right ON Key
| WHERE Language IS NOT NULL 
----