# joinWith

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

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

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

```KOTLIN
joinWith(otherDf, type = JoinType.Inner) { joinExpression }

joinExpression: JoinedDataRow.(JoinedDataRow) -> Boolean

interface JoinedDataRow: LeftDataRow {
    
    val right: RightDataRow
    
}
```

This function is a [join](join.html) variant that lets you match data using any expression that returns a Boolean, which also gives you the opportunity to perform operations that require values from both matching rows. Can be helpful if the data you want to join wasn't designed to be relational and requires heuristics to tell if rows are matching, or has relations other than `equals`.

For example, you can match rows based on:

* Order relations such as `>`, `<`, `in` for numerical or DateTime values

* Spatial relations, like distance within a certain range if your data includes spatial or geographical values

* String equivalence using more complex comparison techniques, such as `contains`, regular expressions, Levenshtein Distance or language models.

## Join types with examples

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

For every join type there is a shortcut operation:

### Inner join

Properties:

```KOTLIN
campaigns.innerJoinWith(visits) {
    right.date in startDate..endDate
}
```

Strings:

```KOTLIN
campaigns.innerJoinWith(visits) {
    right.getValue<LocalDate>("date") in "startDate"<LocalDate>().."endDate"<LocalDate>()
}
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.joinWith.html)

### Filter join

Special case of inner join when you only need the data from the left table.

Properties:

```KOTLIN
campaigns.filterJoinWith(visits) {
    right.date in startDate..endDate
}
```

Strings:

```KOTLIN
campaigns.filterJoinWith(visits) {
    right.getValue<LocalDate>("date") in "startDate"<LocalDate>().."endDate"<LocalDate>()
}
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.filterJoinWith.html)

### Left join

Properties:

```KOTLIN
campaigns.leftJoinWith(visits) {
    right.date in startDate..endDate
}
```

Strings:

```KOTLIN
campaigns.leftJoinWith(visits) {
    right.getValue<LocalDate>("date") in "startDate"<LocalDate>().."endDate"<LocalDate>()
}
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.leftJoinWith.html)

### Right join

Properties:

```KOTLIN
campaigns.rightJoinWith(visits) {
    right.date in startDate..endDate
}
```

Strings:

```KOTLIN
campaigns.rightJoinWith(visits) {
    right.getValue<LocalDate>("date") in "startDate"<LocalDate>().."endDate"<LocalDate>()
}
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.rightJoinWith.html)

### Full join

Properties:

```KOTLIN
campaigns.fullJoinWith(visits) {
    right.date in startDate..endDate
}
```

Strings:

```KOTLIN
campaigns.fullJoinWith(visits) {
    right.getValue<LocalDate>("date") in "startDate"<LocalDate>().."endDate"<LocalDate>()
}
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.fullJoinWith.html)

### Exclude join

Can be viewed as filterJoin with a logically opposite predicate.

Properties:

```KOTLIN
campaigns.excludeJoinWith(visits) {
    right.date in startDate..endDate
}
```

Strings:

```KOTLIN
campaigns.excludeJoinWith(visits) {
    right.getValue<LocalDate>("date") in "startDate"<LocalDate>().."endDate"<LocalDate>()
}
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.excludeJoinWith.html)

### Cross join

It can also be called the cross product of two [DataFrame](dataframe.html) objects.

```KOTLIN
campaigns.joinWith(visits) { true }
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.crossProduct.html)

## Difference from join

[join](join.html) tries to take advantage of the knowledge that data in matching columns is the same (because `equals` is used) to minimize the number of columns in the resulting dataframe.

```KOTLIN
df1.innerJoin(df2, "index", "age")
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.compareInnerColumns.html)

Columns that were used in the condition: `index`, `age` - are present only once. A numerical suffix is used to disambiguate columns that are not used in the condition. Compare it to an equivalent `joinWith`:

```KOTLIN
df1.innerJoinWith(df2) { it["index"] == right["index"] && it["age"] == right["age"] }
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.compareInnerValues.html)

Here columns from both [DataFrame](dataframe.html) objects are presented as is. So [join](join.html) is better suited for `equals` relation, and joinWith is for everything else. Below are two more examples with join types that allow mismatches. Note the difference in `null` values

```KOTLIN
df1.leftJoin(df2, "index", "age")
df1.leftJoinWith(df2) { it["index"] == right["index"] && it["age"] == right["age"] }
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.compareLeft.html)

```KOTLIN
df1.rightJoin(df2, "index", "age")
df1.rightJoinWith(df2) { it["index"] == right["index"] && it["age"] == right["age"] }
```

[Embedded content](resources/org.jetbrains.kotlinx.dataframe.samples.api.JoinWith.compareRight.html)