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.

# innerjoin

Inner join between two tables or timetables

## Syntax

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

## Description

example

````C = innerjoin(A,B)` creates the table or timetable, `C`, as the inner join between `A` and `B` by matching up rows using all the variables with the same name as key variables. You can perform inner joins only on certain combinations of tables and timetables.If `A` is a table, then `B` must be a table. `innerjoin` returns `C` as a table.If `A` is a timetable, then `B` can be either a table or a timetable. `innerjoin` returns `C` as a timetable for either combination of inputs.The inner join retains only the rows that match between `A` and `B` with respect to the key variables. `C` contains all nonkey variables from `A` and `B`.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.```
````C = innerjoin(A,B,Name,Value)` performs the inner-join operation with additional options specified by one or more `Name,Value` pair arguments.For example, you can specify the variables to use as key variables.```

example

``````[C,ia,ib] = innerjoin(___)``` 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;6],... {'cereal';'pizza';'salmon';'cookies';'pizza'},... 'VariableNames',{'Age','FavoriteFood'})```
```A=5×2 table Age FavoriteFood ___ ____________ 5 'cereal' 12 'pizza' 23 'salmon' 2 'cookies' 6 'pizza' ```

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

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

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

`C = innerjoin(A,B)`
```C=5×4 table Age FavoriteFood Calories NutritionGrade ___ ____________ ________ ______________ 5 'cereal' 110 'A-' 2 'cookies' 160 'D' 12 'pizza' 140 'B' 6 'pizza' 140 'B' 23 'salmon' 367 'B' ```

Table `C` is sorted by the key variable, `FavoriteFood`.

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 `innerjoin` function to create a new table, `C`, with data from tables `A` and `B`. Retain only rows whose values in the variable `Key1` 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] = innerjoin(A,B)`
```C=3×3 table Key1 Var1 Var2 ____ ____ ____ 'a' 1 4 'b' 2 5 'e' 11 7 ```
```ia = 3×1 1 2 4 ```
```ib = 3×1 1 2 4 ```

Table `C` is sorted by the values in the key variable, `Key1`, and contains the horizontal concatenation of `A(ia,:)` and `B(ib,'Var2')` .

Create a table, `A`.

`A = table([10;4;2;3;7],[5;4;9;6;1],[10;3;8;8;4])`
```A=5×3 table Var1 Var2 Var3 ____ ____ ____ 10 5 10 4 4 3 2 9 8 3 6 8 7 1 4 ```

Create a table, `B`, with common values in the second variable as the first variable of table `A`.

`B = table([6;1;1;6;8],[2;3;4;5;6])`
```B=5×2 table Var1 Var2 ____ ____ 6 2 1 3 1 4 6 5 8 6 ```

Use the `innerjoin` function to create a new table, `C`, with data from tables `A` and `B`. Use the first variable of `A` and the second variable of `B` as key variables.

`[C,ia,ib] = innerjoin(A,B,'LeftKeys',1,'RightKeys',2)`
```C=3×4 table Var1_A Var2 Var3 Var1_B ______ ____ ____ ______ 2 9 8 6 3 6 8 1 4 4 3 1 ```
```ia = 3×1 3 4 2 ```
```ib = 3×1 1 2 3 ```

Table `C` retains only the rows that match between `A` and `B` with respect to the key variables.

Table `C` contains the horizontal concatenation of `A(ia,:)` and `B(ib,'Var1')`.

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 inner join. `C` matches up the rows with common row times. `C` does not contain any other rows from either timetable.

`C = innerjoin(A,B)`
```C=3×2 timetable Time Var1_A Var1_B _____ ______ ______ 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. `innerjoin` 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. `innerjoin` 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.

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, `innerjoin` 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, `innerjoin` includes all the variables from `B` except the key variables.

## Output Arguments

collapse all

Inner join from `A` and `B`, returned as a table or a timetable. The output table or timetable, `C`, contains one row for each pair of rows in `A` and `B` that share the same combination of values in the key variables. If `A` and `B` contain variables with the same name, `innerjoin` adds a unique suffix to the corresponding variable names in `C`.

In general, if there are `m` rows in `A` and `n` rows in `B` that all contain the same combination of values in the key variables, then `C` contains `m*n` rows for that combination.

`C` is sorted by the values in the key variables and contains the horizontal concatenation of `A(ia,LeftVars)` and `B(ib,RightVars)`. By default, `LeftVars` consists of all the variables of `A`, and `RightVars` consists of all the nonkey variables from `B`. Otherwise, `LefttVars` consists of the variables specified by the `'LeftVariables'` name-value pair argument, and `RightVars` is 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 `C`. For more information, see the Properties sections of `table` or `timetable`.

Index to `A`, returned as a column vector. Each element of `ia` identifies the row in `A` that corresponds to that row in the output table or timetable, `C`.

Index to `B`, returned as a column vector. Each element of `ib` identifies the row in `B` that corresponds to that row in the output table or timetable, `C`.

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, `innerjoin` 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 `innerjoin` copies row labels from `A` to create row labels in `C`.

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

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