--- title: Perform a full outer join description: Use join.full() to perform an full outer join of two streams of data. Full outer joins output a row for all rows in both the left and right input streams and join rows that match according to the on predicate. url: https://docs.influxdata.com/flux/v0/join-data/full-outer/ estimated_tokens: 7231 product: Flux version: v0 --- # Perform a full outer join Use [`join.full()`](/flux/v0/stdlib/join/full/) to perform an full outer join of two streams of data. Full outer joins output a row for all rows in both the **left** and **right** input streams and join rows that match according to the `on` predicate. [](#view-table-illustration-of-a-full-outer-join) View table illustration of a full outer join #### left | | | | | --- | --- | --- | | r1 | ● | ● | | r2 | ● | ● | #### right | | | | | --- | --- | --- | | r1 | ▲ | ▲ | | r3 | ▲ | ▲ | | r4 | ▲ | ▲ | #### Full outer join result | | | | | | | --- | --- | --- | --- | --- | | r1 | ● | ● | ▲ | ▲ | | r2 | ● | ● | | | | r3 | | | ▲ | ▲ | | r4 | | | ▲ | ▲ | ## Use join.full to join your data 1. Import the `join` package. 2. Define the **left** and **right** data streams to join: - Each stream must have one or more columns with common values. Column labels do not need to match, but column values do. - Each stream should have identical [group keys](/flux/v0/get-started/data-model/#group-key). *For more information, see [join data requirements](/flux/v0/join-data/#data-requirements).* 3. Use `join.full()` to join the two streams together. Provide the following required parameters: - `left`: Stream of data representing the left side of the join. - `right`: Stream of data representing the right side of the join. - `on`: [Join predicate](/flux/v0/join-data/#join-predicate-function-on). For example: `(l, r) => l.column == r.column`. - `as`: [Join output function](/flux/v0/join-data/#join-output-function-as) that returns a record with values from each input stream. ##### Account for missing, non-group-key values In a full outer join, it’s possible for either the left (`l`) or right (`r`) to contain *null* values for the columns used in the join operation and default to a default record (group key columns are populated and other columns are *null*). `l` and `r` will never both use default records at the same time. To ensure non-null values are included in the output for non-group-key columns, check for the existence of a value in the `l` or `r` record, and return the value that exists: ```js (l, r) => { id = if exists l.id then l.id else r.id return {_time: l.time, location: r.location, id: id} } ``` The following example uses a filtered selection from the [**machineProduction** sample data set](/flux/v0/stdlib/influxdata/influxdb/sample/data/#set) as the **left** data stream and an ad-hoc table created with [`array.from()`](/flux/v0/stdlib/array/from/) as the **right** data stream. #### Example data grouping The example below ungroups the **left** stream to match the grouping of the **right** stream. After the two streams are joined together, the joined data is grouped by `stationID` and sorted by `_time`. ```js import "array" import "influxdata/influxdb/sample" import "join" left = sample.data(set: "machineProduction") |> filter(fn: (r) => r.stationID == "g1" or r.stationID == "g2" or r.stationID == "g3") |> filter(fn: (r) => r._field == "oil_temp") |> limit(n: 5) right = array.from( rows: [ {station: "g1", opType: "auto", last_maintained: 2021-07-15T00:00:00Z}, {station: "g2", opType: "manned", last_maintained: 2021-07-02T00:00:00Z}, {station: "g4", opType: "auto", last_maintained: 2021-08-04T00:00:00Z}, ], ) join.full( left: left |> group(), right: right, on: (l, r) => l.stationID == r.station, as: (l, r) => { stationID = if exists l.stationID then l.stationID else r.station return { stationID: stationID, _time: l._time, _field: l._field, _value: l._value, opType: r.opType, maintained: r.last_maintained, } }, ) |> group(columns: ["stationID"]) |> sort(columns: ["_time"]) ``` [](#view-example-input-and-output-data) View example input and output data ### Input #### left *`_start` and `_stop` columns have been omitted.* | _time | _measurement | stationID | _field | _value | | --- | --- | --- | --- | --- | | 2021-08-01T00:00:00Z | machinery | g1 | oil_temp | 39.1 | | 2021-08-01T00:00:11.51Z | machinery | g1 | oil_temp | 40.3 | | 2021-08-01T00:00:19.53Z | machinery | g1 | oil_temp | 40.6 | | 2021-08-01T00:00:25.1Z | machinery | g1 | oil_temp | 40.72 | | 2021-08-01T00:00:36.88Z | machinery | g1 | oil_temp | 40.8 | | _time | _measurement | stationID | _field | _value | | --- | --- | --- | --- | --- | | 2021-08-01T00:00:00Z | machinery | g2 | oil_temp | 40.6 | | 2021-08-01T00:00:27.93Z | machinery | g2 | oil_temp | 40.6 | | 2021-08-01T00:00:54.96Z | machinery | g2 | oil_temp | 40.6 | | 2021-08-01T00:01:17.27Z | machinery | g2 | oil_temp | 40.6 | | 2021-08-01T00:01:41.84Z | machinery | g2 | oil_temp | 40.6 | | _time | _measurement | stationID | _field | _value | | --- | --- | --- | --- | --- | | 2021-08-01T00:00:00Z | machinery | g3 | oil_temp | 41.4 | | 2021-08-01T00:00:14.46Z | machinery | g3 | oil_temp | 41.36 | | 2021-08-01T00:00:25.29Z | machinery | g3 | oil_temp | 41.4 | | 2021-08-01T00:00:38.77Z | machinery | g3 | oil_temp | 41.4 | | 2021-08-01T00:00:51.2Z | machinery | g3 | oil_temp | 41.4 | #### right | station | opType | last_maintained | | --- | --- | --- | | g1 | auto | 2021-07-15T00:00:00Z | | g2 | manned | 2021-07-02T00:00:00Z | | g4 | auto | 2021-08-04T00:00:00Z | ### Output | _time | stationID | _field | _value | maintained | opType | | --- | --- | --- | --- | --- | --- | | 2021-08-01T00:00:00Z | g1 | oil_temp | 39.1 | 2021-07-15T00:00:00Z | auto | | 2021-08-01T00:00:11.51Z | g1 | oil_temp | 40.3 | 2021-07-15T00:00:00Z | auto | | 2021-08-01T00:00:19.53Z | g1 | oil_temp | 40.6 | 2021-07-15T00:00:00Z | auto | | 2021-08-01T00:00:25.1Z | g1 | oil_temp | 40.72 | 2021-07-15T00:00:00Z | auto | | 2021-08-01T00:00:36.88Z | g1 | oil_temp | 40.8 | 2021-07-15T00:00:00Z | auto | | _time | stationID | _field | _value | maintained | opType | | --- | --- | --- | --- | --- | --- | | 2021-08-01T00:00:00Z | g2 | oil_temp | 40.6 | 2021-07-02T00:00:00Z | manned | | 2021-08-01T00:00:27.93Z | g2 | oil_temp | 40.6 | 2021-07-02T00:00:00Z | manned | | 2021-08-01T00:00:54.96Z | g2 | oil_temp | 40.6 | 2021-07-02T00:00:00Z | manned | | 2021-08-01T00:01:17.27Z | g2 | oil_temp | 40.6 | 2021-07-02T00:00:00Z | manned | | 2021-08-01T00:01:41.84Z | g2 | oil_temp | 40.6 | 2021-07-02T00:00:00Z | manned | | _time | stationID | _field | _value | maintained | opType | | --- | --- | --- | --- | --- | --- | | 2021-08-01T00:00:00Z | g3 | oil_temp | 41.4 | | | | 2021-08-01T00:00:14.46Z | g3 | oil_temp | 41.36 | | | | 2021-08-01T00:00:25.29Z | g3 | oil_temp | 41.4 | | | | 2021-08-01T00:00:38.77Z | g3 | oil_temp | 41.4 | | | | 2021-08-01T00:00:51.2Z | g3 | oil_temp | 41.4 | | | | _time | stationID | _field | _value | maintained | opType | | --- | --- | --- | --- | --- | --- | | | g4 | | | 2021-08-04T00:00:00Z | auto | #### Things to note about the join output - Because the [right stream](#right-input) does not have rows with the `g3` stationID tag, the joined output includes rows with the `g3` stationID tag from the [left stream](#left-input) with *null* values in columns populated from the **right** stream. - Because the [left stream](#left-input) does not have rows with the `g4` stationID tag, the joined output includes rows with the `g4` stationID tag from the [right stream](#right-input) with *null* values in columns populated from the **left** stream. #### Related - [Troubleshoot join operations](/flux/v0/join-data/troubleshoot-joins/) - [join package](/flux/v0/stdlib/join/) - [join.full() function](/flux/v0/stdlib/join/full/)