Documentation

This is machine translation

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

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this 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 = 

     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 = 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
     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 = 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 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 Table Properties or Timetable Properties.

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.

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

Extended Capabilities

See Also

|

Introduced in R2013b

Was this topic helpful?