# innerjoin

Inner join between two tables

## Syntax

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

## Description

example

````C = innerjoin(A,B)` creates the table, `C`, as the inner join between the tables `A` and `B` by matching up rows using all the variables with the same name as key variables.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`.```
````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

### Inner-Join Operation of Tables with One Variable in Common

Create a table, `A`.

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

### Inner-Join Operation of Tables and Indices to Values

Create a table, `A`.

```A = table({'a' 'b' 'c' 'e' 'h'}',[1 2 3 11 17]',... 'VariableNames',{'Key1' 'Var1'})```
```A = 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 = 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 = 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')` .

### Inner-Join Operation of Tables Using Left and Right Keys

Create a table, `A`.

`A = table([10;4;2;3;7],[5;4;9;6;1],[10;3;8;8;4])`
```A = 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 = 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 = 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')`.

## Input Arguments

collapse all

### `A,B` — Input tablestables

Input tables, specified as tables.

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

### `'Keys'` — Variables to use as keyspositive integer | vector of positive integers | variable name | cell array of variable names | logical vector

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

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

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

### `'LeftKeys'` — Variables to use as keys in `A`positive integer | vector of positive integers | variable name | cell array of variable names | logical vector

Variables to use as keys in `A`, specified as the comma-separated pair consisting of `'LeftKeys'` and a positive integer, vector of positive integers, variable name, cell array of variable names, 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. `innnerjoin` pairs key values based on their order.

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

### `'RightKeys'` — Variables to use as keys in `B`positive integer | vector of positive integers | variable name | cell array of variable names | logical vector

Variables to use as keys in `B`, specified as the comma-separated pair consisting of `'RightKeys'` and a positive integer, vector of positive integers, variable name, cell array of variable names, 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.

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

### `'LeftVariables'` — Variables from `A` to include in `C`positive integer | vector of positive integers | variable name | cell array containing one or more variable names | logical vector

Variables from `A` to include in `C`, specified as the comma-separated pair consisting of `'LeftVariables'` and a positive integer, vector of positive integers, variable name, cell array of variable names, 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`.

### `'RightVariables'` — Variables from `B` to include in `C`positive integer | vector of positive integers | variable name | cell array containing one or more variable names | logical vector

Variables from `B` to include in `C`, specified as the comma-separated pair consisting of `'RightVariables'` and a positive integer, vector of positive integers, variable name, cell array of variable names, 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

### `C` — Inner join from `A` and `B`table

Inner 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 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 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` 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 the output table, `C`. For more information, see `Table Properties`.

### `ia` — Index to `A`column vector

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

### `ib` — Index to `B`column vector

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

collapse all

### Key Variable

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