Inner join between two tables or two timetables
C = innerjoin(A,B)C, as the inner join between A and B by
matching up rows using all the variables with the same name as key variables. A and B are
both tables or both timetables. You cannot perform an inner join between
a timetable and a table.
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.
If A and B are timetables,
then the key variables are the vectors of row times of A and B.
C = innerjoin(A,B,Name,Value)Name,Value pair arguments.
For example, you can specify the variables to use as key variables.
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.
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') .
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').
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 =
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 =
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 =
Time Var1_A Var1_B
_____ ______ ______
2 sec 2 4
4 sec 3 5
6 sec 11 6
A,B — Input tablesInput tables, specified as tables or as timetables.
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.
'Keys',2 uses the second variable
in A and the second variable in B as
key variables.'Keys' — Variables to use as keysVariables 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.
If A and B are timetables,
then 'Keys' must specify the row times of A and B.
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 AVariables 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. innnerjoin pairs
key values based on their order.
If A and B are timetables,
then the value of 'LeftKeys' must specify the row
times of A.
Example: 'LeftKeys',1 uses only the first
variable in A as a key variable.
'RightKeys' — Variables to use as keys in BVariables 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.
If A and B are timetables,
then the value of 'RightKeys' must specify the
row times of B.
Example: 'RightKeys',3 uses only the third
variable in B as a key variable.
'LeftVariables' — Variables from A to include in CVariables 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.
'RightVariables' — Variables from B to include in CVariables 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.
C — Inner join from A and BInner join from A and B,
returned as a tableor 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 the output table, C.
For more information, see Table Properties or Timetable Properties.
ia — Index to AIndex 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.
ib — Index to BIndex 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.
Variable used to match and combine data between
the input tables, A and B.
This function fully supports tall arrays. For more information, see Tall Arrays.