Documentation

This is machine translation

Translated by
Mouseover text to see original. Click the button below to return to the English verison 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=5x2 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=5x3 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=5x4 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=5x2 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=4x2 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=3x3 table Key1 Var1 Var2 ____ ____ ____ 'a' 1 4 'b' 2 5 'e' 11 7 ```
```ia = 1 2 4 ```
```ib = 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=5x3 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=5x2 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=3x4 table Var1_A Var2 Var3 Var1_B ______ ____ ____ ______ 2 9 8 6 3 6 8 1 4 4 3 1 ```
```ia = 3 4 2 ```
```ib = 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=4x1 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=4x1 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=3x2 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 single 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.

collapse all

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, 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, 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, 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, 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, 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.