# Join Operator

## Overview

Enrich data with `join`.

```kusto
| join kind=<kind> <dest>=(<target_query>) on <condition>
```

When a `join` is performed, rows from the pipe are matched up with rows from the `<target_query>` based on the `<condition>`. The resulting data contains all of the original fields from the pipe, with the fields from the `<target_query>` added to the `<dest>` field. The rows returned are controlled with `<kind>`.

### `<target_query>`

The `target_query` is the data that will be joined. It can be any valid query, such as simply a table name `(some_table)` or a more complex statement `(some_table | extend id = a + b)`.

### `<condition>`

The `<condition>` is an expression that is used to control which rows in the pipe are joined to rows in the `<target_query>`. It is required for every join `<kind>` except `cross` joins. Joins have a `$left` side (the pipe) and a `$right` side (the `<target_query>`). For example, a condition of `$left.id == $right.id` will match rows that contain the same value for `id`.

### `<kind>`

Control which rows are emitted with `kind`.

<table><thead><tr><th width="189">kind value</th><th>Description</th></tr></thead><tbody><tr><td><code>inner</code></td><td>Return only rows that match on <code>condition</code>.</td></tr><tr><td><code>leftouter</code></td><td>Return every row from the pipe enriched with the fields from the <code>target_query</code>, rows with no match have <code>null</code> for the columns from <code>target_query</code>.</td></tr><tr><td><code>rightouter</code></td><td>Return every row from the <code>target_query</code> enriched with the fields from the pipe, rows with no match have <code>null</code> for the columns from the pipe.</td></tr><tr><td><code>fullouter</code></td><td>Return rows from both the pipe and the <code>target_query</code>, even those with no match.</td></tr><tr><td><code>cross</code></td><td>Return every row in the pipe combined with every row from the <code>target_query</code> .</td></tr></tbody></table>

## Examples

### Join on IP addresses

Let's say our pipe contains IP addresses in the `sourceIP` field and we want to enrich it with data from a table named `ip_location` containing IP addresses in a field named `ip`.

We can use a `<condition>` of `$left.sourceIp == $right.ip` to match rows based on IP addresses and `kind=leftouter` to return all rows from the pipe—even those that might be missing from `ip_location`.

```kusto
<source_table>
| join kind=leftouter ip=(ip_location) on $left.sourceIP == $right.ip
```

### Join with `tor_exit_nodes` table

This example uses an `inner` join to find `aws_alb` logs that have a `clientIp` that is listed in the Panther-managed [`tor_exit_node` Enrichment Provider](https://docs.panther.com/enrichment/tor-exit-nodes) table.

```kusto
aws_alb
| join kind=inner tor=(panther_lookups.public.tor_exit_nodes) on        
       $left.clientIp == $right.ip
| limit 10
```

### Join with `ipinfo_location_datalake` table using UDFs

Here is a more complex example, in which we'll enrich ALB logs with the Panther-managed [`ipinfo_location_datalake` Enrichment Provider](https://docs.panther.com/enrichment/ipinfo) table.

Note that this query uses the [`snowflake.func`](https://docs.panther.com/functions#snowflake.func) function to call the SQL user-defined functions (UDFs) `panther_lookups.public.ipinfo_to_join_key()` and `panther_lookups.public.ipinfo_to_int()`, which take an IP address as an argument. Learn more about these UDFs on [IPinfo](https://docs.panther.com/enrichment/ipinfo#using-a-joinkey).

```kusto
aws_alb
| where p_event_time > time.ago(1d)
| join kind=leftouter ip=(panther_lookups.public.ipinfo_location_datalake) on        
       snowflake.func('panther_lookups.public.ipinfo_to_join_key', $right.joinKey) == snowflake.func('panther_lookups.public.ipinfo_to_join_key', $left.clientIp)
       and snowflake.func('panther_lookups.public.ipinfo_to_int', $left.clientIp) between 
       snowflake.func('panther_lookups.public.ipinfo_to_int', $right.startIP) .. snowflake.func('panther_lookups.public.ipinfo_to_int', $right.endIP)
| project p_event_time, clientIp, city=ip.city, country=ip.country
| limit 10
```

### Join using `datatable`

It's possible to inject sample data into a `join` using the [`datatable`](https://docs.panther.com/pantherflow/operators/datatable) operator:

```kusto
aws_alb
| join kind=inner system_info=(datatable [{"ip":"192.168.1.1", "hostname":"fluffy"}, {"ip":"192.168.1.2", "hostname":"squishy"}]) on        
       $left.clientIp == $right.ip
| limit 10
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.panther.com/pantherflow/operators/join.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.