# Join Operations

### Overview

The join pipe allows you to combine results from two queries based on common field values. Use joins to correlate data across different log streams, enrich logs with additional context, and more.

### Syntax

```
<left_query> | join by (field1, field2, ...) (<right_query>) [join_type] [prefix <prefix>]
```

**Components:**

* **left\_query** - The main query whose results form the left side of the join
* **by (fields)** - One or more fields to join on (must exist in both queries)
* **right\_query** - The subquery whose results form the right side of the join
* **join\_type** - Optional: `inner`, `left`, `right`, or `full` (default: `full`)
* **prefix** - Optional: Add a prefix to fields from the right query to avoid name conflicts

### Join Types

#### INNER JOIN

Returns only records that have matching values in both queries.

```
* | join by (user_id) (status:active) inner
```

*Only logs where user\_id exists in both queries*

```
* | join by (trace_id) (error:"*") inner
```

*Only traces that have errors*

#### LEFT JOIN

Returns all records from the left query and matched records from the right query. Right side shows NULL when no match.

```
* | join by (request_id) (status_code:>=500) left
```

*All logs, with error status enrichment where available*

```
workload:api | join by (session_id) (path:/checkout) left
```

*All API logs, with checkout path data where it exists*

```
a:val1 | join by (id) (b:val2) left
```

*All records matching a:val1, enriched with b:val2 data where id matches*

#### RIGHT JOIN

Returns all records from the right query and matched records from the left query. Left side shows NULL when no match.

```
* | join by (session_id) (error:true) right
```

*All error logs, with context from main query where session\_id matches*

```
workload:frontend | join by (user_id) (conversion:true) right
```

*All conversions, with frontend logs where user\_id matches*

### Single Field Join

Join on a single field that exists in both queries.

```
* | join by (trace_id) (service:payment)
```

*Match logs by trace\_id*

```
* | join by (user_id) (role:admin) inner
```

*Only logs from admin users*

### Multiple Field Join

Join on multiple fields for more precise matching.

```
* | join by (user_id, session_id) (path:/api/*) inner
```

*Match on both user\_id AND session\_id*

```
* | join by (namespace, workload) (status:Running) inner
```

*Match on namespace AND workload*

### Joining Aggregated Results

Join pre-aggregated data from both queries.

#### Basic Aggregation Join

```
* | stats by (user_id) count() as left_count | join by (user_id) (* | stats by (user_id) sum(amount) as total_amount) inner
```

*Count and sum joined by user\_id*

#### Calculate Ratios

```
level:error | stats by (service) count() errors | join by (service) (level:info | stats by (service) count() total) | math errors / total error_rate
```

*Calculate error rate per service*

### Joining with Filters

Combine joins with complex filters and other pipes.

```
level:error | limit 100 | join by (request_id) (status_code:>=500 | limit 50)
```

*Top 100 errors joined with top 50 server errors*

### Chaining Joins with Other Pipes

Combine joins with math, sort, fields, and other operations.

#### With Math Expressions

```
level:info | stats by (workload) count() total_info | join by (workload) (level:error | stats by (workload) count() total_error) | math total_error / total_info error_ratio
```

*Calculate error ratio per workload*

#### With Sort

```
level:info | stats by (workload) count() total_info | join by (workload) (level:debug | stats by (workload) count() total_debug) | math total_info / total_debug res | sort by (res desc)
```

*Calculate and sort by ratio*

### Prefix for Field Name Conflicts

When both queries have fields with the same name, use `prefix` to avoid conflicts.

```
* | join by (user_id) (service:payment) prefix payment_
```

*Payment service fields prefixed with "payment\_"*