Documentation

### This is machine translation

Translated by
Mouseover text to see original. Click the button below to return to the English version of the page.

Note: This page has been translated by MathWorks. Click here to see
To view all translated materials including this page, select Country from the country navigator on the bottom of this page.

# outerjoin

Outer join between two tables or timetables

## Syntax

``C = outerjoin(A,B)``
``C = outerjoin(A,B,Name,Value)``
``````[C,ia,ib] = outerjoin(___)``````

## Description

example

````C = outerjoin(A,B)` creates the table or timetable, `C`, as the outer join between `A` and `B` by matching up rows using all the variables with the same name as key variables. You can perform outer joins only on certain combinations of tables and timetables.If `A` is a table, then `B` must be a table. `outerjoin` returns `C` as a table.If `A` is a timetable, then `B` can be either a table or a timetable. `outerjoin` returns `C` as a timetable for either combination of inputs.The outer join includes the rows that match between `A` and `B`, and also unmatched rows from either `A` or `B`, all with respect to the key variables. `C` contains all variables from both `A` and `B`, including the key variables.The row labels of `A` and `B` can be key variables. Row labels are the row names of a table, or the row times of a timetable.```

example

````C = outerjoin(A,B,Name,Value)` performs the outer-join operation with additional options specified by one or more `Name,Value` pair arguments.```

example

``````[C,ia,ib] = outerjoin(___)``` also returns index vectors, `ia` and `ib`, indicating the correspondence between rows in `C` and those in `A` and `B` respectively. You can use this syntax with any of the input arguments in the previous syntaxes.```

## Examples

collapse all

Create a table, `A`.

```A = table([5;12;23;2;15;6],... {'cheerios';'pizza';'salmon';'oreos';'lobster';'pizza'},... 'VariableNames',{'Age','FavoriteFood'},... 'RowNames',{'Amy','Bobby','Holly','Harry','Marty','Sally'})```
```A=6×2 table Age FavoriteFood ___ ____________ Amy 5 'cheerios' Bobby 12 'pizza' Holly 23 'salmon' Harry 2 'oreos' Marty 15 'lobster' Sally 6 'pizza' ```

Create a table, `B`, with one variable in common with `A`, called `FavoriteFood`.

```B = table({'cheerios';'oreos';'pizza';'salmon';'cake'},... [110;160;140;367;243],... {'A-';'D';'B';'B';'C-'},... 'VariableNames',{'FavoriteFood','Calories','NutritionGrade'})```
```B=5×3 table FavoriteFood Calories NutritionGrade ____________ ________ ______________ 'cheerios' 110 'A-' 'oreos' 160 'D' 'pizza' 140 'B' 'salmon' 367 'B' 'cake' 243 'C-' ```

Use the `outerjoin` function to create a new table, `C`, with data from tables `A` and `B`.

`C = outerjoin(A,B)`
```C=7×5 table Age FavoriteFood_A FavoriteFood_B Calories NutritionGrade ___ ______________ ______________ ________ ______________ NaN '' 'cake' 243 'C-' 5 'cheerios' 'cheerios' 110 'A-' 15 'lobster' '' NaN '' 2 'oreos' 'oreos' 160 'D' 12 'pizza' 'pizza' 140 'B' 6 'pizza' 'pizza' 140 'B' 23 'salmon' 'salmon' 367 'B' ```

Table `C` contains a separate variable for the key variable from `A`, called `FavoriteFood_A`, and the key variable from `B`, called `FavoriteFood_B`.

Create a table, `A`.

```A = table({'a' 'b' 'c' 'e' 'h'}',[1 2 3 11 17]',... 'VariableNames',{'Key1' 'Var1'})```
```A=5×2 table Key1 Var1 ____ ____ 'a' 1 'b' 2 'c' 3 'e' 11 'h' 17 ```

Create a table, `B`, with common values in the variable `Key1` between tables `A` and `B`, but also containing rows with values of `Key1` not present in `A`.

```B = table({'a','b','d','e'}',[4;5;6;7],... 'VariableNames',{'Key1' 'Var2'})```
```B=4×2 table Key1 Var2 ____ ____ 'a' 4 'b' 5 'd' 6 'e' 7 ```

Use the `outerjoin` function to create a new table, `C`, with data from tables `A` and `B`. Merge the key values into a single variable in the output table, `C`.

`C = outerjoin(A,B,'MergeKeys',true)`
```C=6×3 table Key1 Var1 Var2 ____ ____ ____ 'a' 1 4 'b' 2 5 'c' 3 NaN 'd' NaN 6 'e' 11 7 'h' 17 NaN ```

Variables in table `C` that came from `A` contain null values in the rows that have no match from `B`. Similarly, variables in `C` that came from `B` contain null values in those rows that had no match from `A`.

Create a table, `A`.

```A = table({'a' 'b' 'c' 'e' 'h'}',[1 2 3 11 17]',... 'VariableNames',{'Key1' 'Var1'})```
```A=5×2 table Key1 Var1 ____ ____ 'a' 1 'b' 2 'c' 3 'e' 11 'h' 17 ```

Create a table, `B`, with common values in the variable `Key1` between tables `A` and `B`, but also containing rows with values of `Key1` not present in `A`.

```B = table({'a','b','d','e'}',[4;5;6;7],... 'VariableNames',{'Key1' 'Var2'})```
```B=4×2 table Key1 Var2 ____ ____ 'a' 4 'b' 5 'd' 6 'e' 7 ```

Use the `outerjoin` function to create a new table, `C`, with data from tables `A` and `B`. Match up rows with common values in the key variable, `Key1`, but also retain rows whose key values don’t have a match.

Also, return index vectors, `ia` and `ib` indicating the correspondence between rows in `C` and rows in `A` and `B` respectively.

`[C,ia,ib] = outerjoin(A,B)`
```C=6×4 table Key1_A Var1 Key1_B Var2 ______ ____ ______ ____ 'a' 1 'a' 4 'b' 2 'b' 5 'c' 3 '' NaN '' NaN 'd' 6 'e' 11 'e' 7 'h' 17 '' NaN ```
```ia = 6×1 1 2 3 0 4 5 ```
```ib = 6×1 1 2 0 3 4 0 ```

The index vectors `ia` and `ib` contain zeros to indicate the rows in table `C` that do not correspond to rows in tables `A` or `B`, respectively.

Create a table, `A`.

```A = table({'a' 'b' 'c' 'e' 'h'}',[1 2 3 11 17]',... 'VariableNames',{'Key1' 'Var1'})```
```A=5×2 table Key1 Var1 ____ ____ 'a' 1 'b' 2 'c' 3 'e' 11 'h' 17 ```

Create a table, `B`, with common values in the variable `Key1` between tables `A` and `B`, but also containing rows with values of `Key1` not present in `A`.

```B = table({'a','b','d','e'}',[4;5;6;7],... 'VariableNames',{'Key1' 'Var2'})```
```B=4×2 table Key1 Var2 ____ ____ 'a' 4 'b' 5 'd' 6 'e' 7 ```

Use the `outerjoin` function to create a new table, `C`, with data from tables `A` and `B`. Ignore rows in `B` whose key values do not match any rows in `A`.

Also, return index vectors, `ia` and `ib` indicating the correspondence between rows in `C` and rows in `A` and `B` respectively.

`[C,ia,ib] = outerjoin(A,B,'Type','left')`
```C=5×4 table Key1_A Var1 Key1_B Var2 ______ ____ ______ ____ 'a' 1 'a' 4 'b' 2 'b' 5 'c' 3 '' NaN 'e' 11 'e' 7 'h' 17 '' NaN ```
```ia = 5×1 1 2 3 4 5 ```
```ib = 5×1 1 2 0 4 0 ```

All values of `ia` are nonzero indicating that all rows in `C` have corresponding rows in `A`.

Create two timetables, `A` and `B`. They have some row times in common, but each also includes row times that are not in the other timetable.

`A = timetable(seconds([1;2;4;6]),[1 2 3 11]')`
```A=4×1 timetable Time Var1 _____ ____ 1 sec 1 2 sec 2 4 sec 3 6 sec 11 ```
`B = timetable(seconds([2;4;6;7]),[4 5 6 7]')`
```B=4×1 timetable Time Var1 _____ ____ 2 sec 4 4 sec 5 6 sec 6 7 sec 7 ```

Combine `A` and `B` with an outer join. `C` matches up the rows with common row times, but also includes the rows that do not have matches.

`C = outerjoin(A,B)`
```C=5×2 timetable Time Var1_A Var1_B _____ ______ ______ 1 sec 1 NaN 2 sec 2 4 4 sec 3 5 6 sec 11 6 7 sec NaN 7 ```

Combine `A` and `B`, but ignore rows in `B` whose row times do not match any row times in `A`.

`D = outerjoin(A,B,'Type','left')`
```D=4×2 timetable Time Var1_A Var1_B _____ ______ ______ 1 sec 1 NaN 2 sec 2 4 4 sec 3 5 6 sec 11 6 ```

## Input Arguments

collapse all

Input tables, specified as tables or as timetables.

### Name-Value Pair Arguments

Specify optional comma-separated pairs of `Name,Value` arguments. `Name` is the argument name and `Value` is the corresponding value. `Name` must appear inside quotes. You can specify several name and value pair arguments in any order as `Name1,Value1,...,NameN,ValueN`.

Example: `'Keys',2` uses the second variable in `A` and the second variable in `B` as key variables.

Variables to use as keys, specified as the comma-separated pair consisting of `'Keys'` and a positive integer, vector of positive integers, character vector, cell array of character vectors, string array, or logical vector.

You cannot use the `'Keys'` name-value pair argument with the `'LeftKeys'` and `'RightKeys'` name-value pair arguments.

Row labels can be keys, alone or in combination with other key variables. For more information, see the Tips section.

Example: `'Keys',[1 3]` uses the first and third variables in `A` and `B` as a key variables.

Variables to use as keys in `A`, specified as the comma-separated pair consisting of `'LeftKeys'` and a positive integer, vector of positive integers, character vector, cell array of character vectors, string array, or logical vector.

You must use the `'LeftKeys'` name-value pair argument in conjunction with the `'RightKeys'` name-value pair argument. `'LeftKeys'` and `'RightKeys'` both must specify the same number of key variables. `outerjoin` pairs key values based on their order.

Row labels can be keys, alone or in combination with other key variables. For more information, see the Tips section.

Example: `'LeftKeys',1` uses only the first variable in `A` as a key variable.

Variables to use as keys in `B`, specified as the comma-separated pair consisting of `'RightKeys'` and a positive integer, vector of positive integers, character vector, cell array of character vectors, string array, or logical vector.

You must use the `'RightKeys'` name-value pair argument in conjunction with the `'LeftKeys'` name-value pair argument. `'LeftKeys'` and `'RightKeys'` both must specify the same number of key variables. `outerjoin` pairs key values based on their order.

Row labels can be keys, alone or in combination with other key variables. For more information, see the Tips section.

Example: `'RightKeys',3` uses only the third variable in `B` as a key variable.

Merge keys flag, specified as the comma-separated pair consisting of `'MergeKeys'` and either `false`, `true`, `0` or `1`.

 `false` `outerjoin` includes two separate variables in the output table, `C`, for each key variable pair from tables `A` and `B`.This is the default behavior. `true` `outerjoin` includes a single variable in the output table, `C`, for each key variable pair from tables `A` and `B`.`outerjoin` creates the single variable by merging the key values from `A` and `B`, taking values from `A` where a corresponding row exists in `A`, and taking values from `B` otherwise.If you specify `'LeftVariables'` or `'RightVariables'` to include only one key from a key variable pair, then `outerjoin` includes the merged key—containing values from both key variables—in the output table.If you specify `'LeftVariables'` and `'RightVariables'` to exclude both keys from a key variable pair, then `outerjoin` does not include the merged key variable in the output table.

Variables from `A` to include in `C`, specified as the comma-separated pair consisting of `'LeftVariables'` and a positive integer, vector of positive integers, character vector, cell array of character vectors, string array, or logical vector.

You can use `'LeftVariables'` to include or exclude key variables as well as nonkey variables from the output, `C`.

By default, `outerjoin` includes all variables from `A`.

Variables from `B` to include in `C`, specified as the comma-separated pair consisting of `'RightVariables'` and a positive integer, vector of positive integers, character vector, cell array of character vectors, string array, or logical vector.

You can use `'RightVariables'` to include or exclude key variables as well as nonkey variables from the output, `C`.

By default, `outerjoin` includes all the variables from `B`.

Type of outer-join operation, specified as the comma-separated pair consisting of `'Type'` and either `'full'`, `'left'`, or `'right'`.

• For a left outer join, `C` contains rows corresponding to key values in `A` that do not match any values in `B`, but not vice-versa.

• For a right outer join, `C` contains rows corresponding to key values in `B` that do not match any values in `A`, but not vice-versa.

By default, `outerjoin` does a full outer join and includes unmatched rows from both `A` and `B`.

## Output Arguments

collapse all

Outer join from `A` and `B`, returned as a table. The output table, `C`, contains one row for each pair of rows in tables `A` and `B` that share the same combination of key values. If `A` and `B` contain variables with the same name, `outerjoin` adds a unique suffix to the corresponding variable names in `C`. Variables in `C` that came from `A` contain null values in those rows that had no match from `B`. Similarly, variables in `C` that came from `B` contain null values in those rows that had no match from `A`.

In general, if there are `m` rows in table `A` and `n` rows in table `B` that all contain the same combination of values in the key variables, table `C` contains `m*n` rows for that combination. `C` also contains rows corresponding to key value combinations in one input table that do not match any row the other input table.

`C` contains the horizontal concatenation of `A(ia,LeftVars)` and `B(ib,RightVars)` sorted by the values in the key variables. By default, `LeftVars` consists of all the variables of `A`, and `RightVars` consists of all the from `B`. Otherwise, `LeftVars` consists of the variables specified by the `'LeftVariables'` name-value pair argument, and `RightVars` consists of the variables specified by the `'RightVariables'` name-value pair argument.

You can store additional metadata such as descriptions, variable units, variable names, and row names in the table. For more information, see the Properties section of `table`.

Index to `A`, returned as a column vector. Each element of `ia` identifies the row in table `A` that corresponds to that row in the output table, `C`. The vector `ia` contains zeros to indicate the rows in `C` that do not correspond to rows in `A`.

Index to `B`, returned as a column vector. Each element of `ib` identifies the row in table `B` that corresponds to that row in the output table, `C`. The vector `ib` contains zeros to indicate the rows in `C` that do not correspond to rows in `B`.

## More About

collapse all

### Key Variable

Variable used to match and combine data between the input tables, `A` and `B`.

## Tips

Row labels from the input tables `A` and `B` can be keys, alone or in combination with other key variables. Row labels are the row names of a table or the row times of a timetable. In general, `outerjoin` copies row labels from the input table `A` to the output table `C`.

• If `A` has no row labels, then `C` has no row labels.

• If `A` has row labels, then `outerjoin` copies row labels from `A` to create row labels in `C`.

• If you specify row labels from both `A` and `B` as a key pair, then `outerjoin` merges row labels from `B` into row labels of `C` where needed.

• If you specify row labels of `A` as a key, but do not specify row labels of `B` as the matching key, then `outerjoin` creates default row labels in `C` where needed.

• If both `A` and `B` are tables, but you do not specify either input table’s row names as a key, then `outerjoin` does not create row names in `C`.

You cannot perform an outer join using the row labels of `A` as the left key and a variable of `B` as the right key. To perform the outer join, convert the row labels of `A` to a table variable and use the new table variable as a key.

Download eBook