---
title: join.tables() function
description: join.tables() joins two input streams together using a specified method, predicate, and a function to join two corresponding records, one from each input stream.
url: https://docs.influxdata.com/flux/v0/stdlib/join/tables/
estimated_tokens: 2445
product: Flux
version: v0
publisher: InfluxData
canonical: https://docs.influxdata.com/flux/v0/stdlib/join/tables/
date: '2024-04-08T16:01:02-06:00'
lastmod: '2024-04-08T16:01:02-06:00'
---

* Flux 0.172.0+

InfluxDB support

`join.tables()` joins two input streams together using a specified method, predicate, and a function to join two corresponding records, one from each input stream.

`join.tables()` only compares records with the same group key. Output tables have the same grouping as the input tables.

##### Function type signature

```js
(
    <-left: stream[A],
    as: (l: A, r: B) => C,
    method: string,
    on: (l: A, r: B) => bool,
    right: stream[B],
) => stream[C] where A: Record, B: Record, C: Record
```

For more information, see [Function type signatures](/flux/v0/function-type-signatures/).

## Parameters

### left

Left input stream. Default is piped-forward data (`<-`).

### right

(Required)
Right input stream.

### on

(Required)
Function that takes a left and right record (`l`, and `r` respectively), and returns a boolean.

The body of the function must be a single boolean expression, consisting of one
or more equality comparisons between a property of `l` and a property of `r`,
each chained together by the `and` operator.

### as

(Required)
Function that takes a left and a right record (`l` and `r` respectively), and returns a record.
The returned record is included in the final output.

### method

(Required)
String that specifies the join method.

**Supported methods:**

* inner
* left
* right
* full

## Examples

* [Perform an inner join](#perform-an-inner-join)
* [Perform a left outer join](#perform-a-left-outer-join)
* [Perform a right outer join](#perform-a-right-outer-join)
* [Perform a full outer join](#perform-a-full-outer-join)

### Perform an inner join

```js
import "sampledata"
import "join"

ints = sampledata.int()
strings = sampledata.string()

join.tables(
    method: "inner",
    left: ints,
    right: strings,
    on: (l, r) => l._time == r._time,
    as: (l, r) => ({l with label: r._value}),
)
```

[](#view-example-output)

View example output

#### Output data

|       \_time       |\_value|   label    |\*tag|
|--------------------|-------|------------|-----|
|2021-01-01T00:00:00Z|  \-2  |smpl\_g9qczs| t1  |
|2021-01-01T00:00:10Z|  10   |smpl\_0mgv9n| t1  |
|2021-01-01T00:00:20Z|   7   |smpl\_phw664| t1  |
|2021-01-01T00:00:30Z|  17   |smpl\_guvzy4| t1  |
|2021-01-01T00:00:40Z|  15   |smpl\_5v3cce| t1  |
|2021-01-01T00:00:50Z|   4   |smpl\_s9fmgy| t1  |

|       \_time       |\_value|   label    |\*tag|
|--------------------|-------|------------|-----|
|2021-01-01T00:00:00Z|  19   |smpl\_b5eida| t2  |
|2021-01-01T00:00:10Z|   4   |smpl\_eu4oxp| t2  |
|2021-01-01T00:00:20Z|  \-3  |smpl\_5g7tz4| t2  |
|2021-01-01T00:00:30Z|  19   |smpl\_sox1ut| t2  |
|2021-01-01T00:00:40Z|  13   |smpl\_wfm757| t2  |
|2021-01-01T00:00:50Z|   1   |smpl\_dtn2bv| t2  |

### Perform a left outer join

If the join method is anything other than `inner`, pay special attention to how
the output record is constructed in the `as` function.

Because of how flux handles outer joins, it’s possible for either `l` or `r` to be a
default record. This means any value in a non-group-key column could be null.

For more information about the behavior of outer joins, see the [Outer joins](/flux/v0/stdlib/join/#outer-joins)section in the `join` package documentation.

In the case of a left outer join, `l` is guaranteed to not be a default record. To
ensure that the output record has non-null values for any columns that aren’t part
of the group key, use values from `l`. Using a non-group-key value from `r` risks
that value being null.

The example below constructs the output record almost entirely from properties of `l`.
The only exception is the `v_right` column which gets its value from `r._value`.
In this case, understand and expect that `v_right` will sometimes be null.

```js
import "array"
import "join"

left =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 1, label: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 2, label: "b"},
            {_time: 2022-01-01T00:00:00Z, _value: 3, label: "d"},
        ],
    )
right =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 0.4, id: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.5, id: "c"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.6, id: "d"},
        ],
    )

join.tables(
    method: "left",
    left: left,
    right: right,
    on: (l, r) => l.label == r.id and l._time == r._time,
    as: (l, r) => ({_time: l._time, label: l.label, v_left: l._value, v_right: r._value}),
)
```

[](#view-example-output)

View example output

#### Output data

|       \_time       |label|v\_left|v\_right|
|--------------------|-----|-------|--------|
|2022-01-01T00:00:00Z|  a  |   1   |  0.4   |
|2022-01-01T00:00:00Z|  b  |   2   |        |
|2022-01-01T00:00:00Z|  d  |   3   |  0.6   |

### Perform a right outer join

The next example is nearly identical to the [previous example](#perform-a-left-outer-join),
but uses the `right` join method. With this method, `r` is guaranteed to not be a default
record, but `l` may be a default record. Because `l` is more likely to contain null values,
the output record is built almost entirely from properties of `r`, with the exception of`v_left`, which we expect to sometimes be null.

```js
import "array"
import "join"

left =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 1, label: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 2, label: "b"},
            {_time: 2022-01-01T00:00:00Z, _value: 3, label: "d"},
        ],
    )
right =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 0.4, id: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.5, id: "c"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.6, id: "d"},
        ],
    )

join.tables(
    method: "right",
    left: left,
    right: right,
    on: (l, r) => l.label == r.id and l._time == r._time,
    as: (l, r) => ({_time: r._time, label: r.id, v_left: l._value, v_right: r._value}),
)
```

[](#view-example-output)

View example output

#### Output data

|       \_time       |label|v\_left|v\_right|
|--------------------|-----|-------|--------|
|2022-01-01T00:00:00Z|  a  |   1   |  0.4   |
|2022-01-01T00:00:00Z|  c  |       |  0.5   |
|2022-01-01T00:00:00Z|  d  |   3   |  0.6   |

### Perform a full outer join

In a full outer join, there are no guarantees about `l` or `r`. Either one of them could
be a default record, but they will never both be a default record at the same time.

To get non-null values for the output record, check both `l` and `r` to see which contains
the desired values.

The example below defines a function for the `as` parameter that appropriately handles
the uncertainty of a full outer join.

`v_left` and `v_right` still use values from `l` and `r` directly, because we expect
them to sometimes be null in the output table.

```js
import "array"
import "join"

left =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 1, label: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 2, label: "b"},
            {_time: 2022-01-01T00:00:00Z, _value: 3, label: "d"},
        ],
    )
right =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 0.4, id: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.5, id: "c"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.6, id: "d"},
        ],
    )

join.tables(
    method: "full",
    left: left,
    right: right,
    on: (l, r) => l.label == r.id and l._time == r._time,
    as: (l, r) => {
        time = if exists l._time then l._time else r._time
        label = if exists l.label then l.label else r.id

        return {_time: time, label: label, v_left: l._value, v_right: r._value}
    },
)
```

[](#view-example-output)

View example output

#### Output data

|       \_time       |label|v\_left|v\_right|
|--------------------|-----|-------|--------|
|2022-01-01T00:00:00Z|  a  |   1   |  0.4   |
|2022-01-01T00:00:00Z|  b  |   2   |        |
|2022-01-01T00:00:00Z|  c  |       |  0.5   |
|2022-01-01T00:00:00Z|  d  |   3   |  0.6   |

#### Related

* [Join data](/flux/v0/join-data/)
* [Troubleshoot join operations](/flux/v0/join-data/troubleshoot-joins/)

[transformations](/flux/v0/tags/transformations/)
| _time | _value | label | *tag |
| --- | --- | --- | --- |
| _time | _value | label | *tag |
| 2021-01-01T00:00:00Z | -2 | smpl_g9qczs | t1 |
| 2021-01-01T00:00:10Z | 10 | smpl_0mgv9n | t1 |
| 2021-01-01T00:00:20Z | 7 | smpl_phw664 | t1 |
| 2021-01-01T00:00:30Z | 17 | smpl_guvzy4 | t1 |
| 2021-01-01T00:00:40Z | 15 | smpl_5v3cce | t1 |
| 2021-01-01T00:00:50Z | 4 | smpl_s9fmgy | t1 |

| _time | _value | label | *tag |
| --- | --- | --- | --- |
| _time | _value | label | *tag |
| 2021-01-01T00:00:00Z | 19 | smpl_b5eida | t2 |
| 2021-01-01T00:00:10Z | 4 | smpl_eu4oxp | t2 |
| 2021-01-01T00:00:20Z | -3 | smpl_5g7tz4 | t2 |
| 2021-01-01T00:00:30Z | 19 | smpl_sox1ut | t2 |
| 2021-01-01T00:00:40Z | 13 | smpl_wfm757 | t2 |
| 2021-01-01T00:00:50Z | 1 | smpl_dtn2bv | t2 |

| _time | label | v_left | v_right |
| --- | --- | --- | --- |
| _time | label | v_left | v_right |
| 2022-01-01T00:00:00Z | a | 1 | 0.4 |
| 2022-01-01T00:00:00Z | b | 2 |  |
| 2022-01-01T00:00:00Z | d | 3 | 0.6 |

| _time | label | v_left | v_right |
| --- | --- | --- | --- |
| _time | label | v_left | v_right |
| 2022-01-01T00:00:00Z | a | 1 | 0.4 |
| 2022-01-01T00:00:00Z | c |  | 0.5 |
| 2022-01-01T00:00:00Z | d | 3 | 0.6 |

| _time | label | v_left | v_right |
| --- | --- | --- | --- |
| _time | label | v_left | v_right |
| 2022-01-01T00:00:00Z | a | 1 | 0.4 |
| 2022-01-01T00:00:00Z | b | 2 |  |
| 2022-01-01T00:00:00Z | c |  | 0.5 |
| 2022-01-01T00:00:00Z | d | 3 | 0.6 |