# join

> For the complete documentation index, see [llms.txt](https://kotlin.github.io/dataframe/llms.txt).

Joins two [DataFrame](dataframe.html) objects by join columns.

API reference: [join](https://kotlin.github.io/dataframe/api-docs/core/org.jetbrains.kotlinx.dataframe.api/join.html)

A join creates a new dataframe by combining rows from two input dataframes according to one or more key columns.

Rows are merged when the values in the join columns match.

If there is no match, whether the row is included and how missing values are filled depends on the type of join (e.g., inner, left, right, full).

Returns a new [DataFrame](dataframe.html) that contains the merged rows and columns from both inputs.

```KOTLIN
join(otherDf, type = JoinType.Inner) [ { joinColumns } ]

joinColumns: JoinDsl.(LeftDataFrame) -> Columns

interface JoinDsl: LeftDataFrame {
    
    val right: RightDataFrame
    
    fun DataColumn.match(rightColumn: DataColumn)
}
```

`joinColumns` is a special case of [columns selector](columnselectors.html) that defines column mapping for join.

Related operations: [Multiple DataFrames](multipledataframes.html)

## Examples

### Join with explicit keys (with different names)

Use the Join DSL when the key column names differ:

* access the right `DataFrame` via `right`;

* define the join condition with `match`.

```KOTLIN
dfAges
```

[Embedded content](./resources/notebook_test_join_3.html)

```KOTLIN
dfCities
```

[Embedded content](./resources/notebook_test_join_5.html)

```KOTLIN
// INNER JOIN on differently named keys:
// Merge a row when dfAges.firstName == dfCities.name.
// With the given data all 3 names match → all rows merge.
dfAges.join(dfCities) { firstName match right.name }
```

[Embedded content](./resources/notebook_test_join_6.html)

### Join with explicit keys (with the same names)

If mapped columns have the same name, just select join columns (one or several) from the left [DataFrame](dataframe.html):

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_8.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_10.html)

```KOTLIN
// INNER JOIN on "name" only:
// Merge when left.name == right.name.
// Duplicate keys produce multiple merged rows (one per pairing).
dfLeft.join(dfRight) { name }
```

[Embedded content](./resources/notebook_test_join_11.html)

> **Note:**
> In this example, the "city" columns from the left and right dataframes do not match each other. After joining, the "city" column from the right dataframe is included in the result dataframe with the name "city1" to avoid a name conflict.

### Join with implicit keys (all columns with the same name)

If `joinColumns` is not specified, columns with the same name from both [DataFrame](dataframe.html) objects will be used as join columns:

```KOTLIN
dfLeft
```

[Embedded content](./resources/dfLeftImplicit.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/dfRightImplicit.html)

```KOTLIN
// INNER JOIN on all same-named columns ("name" and "city"):
// Merge when BOTH name AND city are equal; otherwise the row is dropped.
dfLeft.join(dfRight)
```

[Embedded content](./resources/notebook_test_join_12.html)

## Join types

Supported join types:

* `Inner` (default) — only matched rows from left and right [DataFrame](dataframe.html) objects

* `Filter` — only matched rows from left [DataFrame](dataframe.html)

* `Left` — all rows from left [DataFrame](dataframe.html), mismatches from right [DataFrame](dataframe.html) filled with `null`

* `Right` — all rows from right [DataFrame](dataframe.html), mismatches from left [DataFrame](dataframe.html) filled with `null`

* `Full` — all rows from left and right [DataFrame](dataframe.html) objects, any mismatches filled with `null`

* `Exclude` — only mismatched rows from left [DataFrame](dataframe.html)

For every join type there is a shortcut operation:

```KOTLIN
df.innerJoin(otherDf) [ { joinColumns } ]
df.filterJoin(otherDf) [ { joinColumns } ]
df.leftJoin(otherDf) [ { joinColumns } ]
df.rightJoin(otherDf) [ { joinColumns } ]
df.fullJoin(otherDf) [ { joinColumns } ]
df.excludeJoin(otherDf) [ { joinColumns } ]
```

### Examples

#### Inner

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_13.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_14.html)

```KOTLIN
// INNER JOIN:
// Combines columns from the left and right dataframes
// and keep only rows where (name, city) matches on both sides.
dfLeft.innerJoin(dfRight) { name and city }
```

[Embedded content](./resources/notebook_test_join_15.html)

#### Filter

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_13.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_14.html)

```KOTLIN
// FILTER JOIN:
// Keep ONLY left rows that have ANY match on (name, city).
// No right-side columns are added.
dfLeft.filterJoin(dfRight) { name and city }
```

[Embedded content](./resources/notebook_test_join_16.html)

#### Left

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_13.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_14.html)

```KOTLIN
// LEFT JOIN:
// Keep ALL left rows and add columns from the right dataframe.
// If (name, city) matches, attach right columns values from
// the corresponding row in the right dataframe;
// if not (e.g. ("Bob", "Dubai") row), fill them with `null`.
dfLeft.leftJoin(dfRight) { name and city }
```

[Embedded content](./resources/notebook_test_join_17.html)

#### Right

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_13.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_14.html)

```KOTLIN
// RIGHT JOIN:
// Keep ALL right rows and add columns from the left dataframe.
// If (name, city) matches, attach left columns values from
// the corresponding row in the left dataframe;
// if not (e.g. ("Bob", "Tokyo") row), fill them with `null`.
dfLeft.rightJoin(dfRight) { name and city }
```

[Embedded content](./resources/notebook_test_join_18.html)

#### Full

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_13.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_14.html)

```KOTLIN
// FULL JOIN:
// Keep ALL rows from both sides. Where there's no match on (name, city),
// the other side is filled with nulls.
dfLeft.fullJoin(dfRight) { name and city }
```

[Embedded content](./resources/notebook_test_join_19.html)

#### Exclude

```KOTLIN
dfLeft
```

[Embedded content](./resources/notebook_test_join_13.html)

```KOTLIN
dfRight
```

[Embedded content](./resources/notebook_test_join_14.html)

```KOTLIN
// EXCLUDE JOIN:
// Keep ONLY left rows that have NO match on (name, city).
// Useful to find "unpaired" left rows.
dfLeft.excludeJoin(dfRight) { name and city }
```

[Embedded content](./resources/notebook_test_join_20.html)