Documentation Center

  • Trials
  • Product Updates

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

expand 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 theinnerjoin 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

expand 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 Apositive 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 Bpositive 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 Cpositive 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 Cpositive 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

expand all

C — Inner join from A and Btable

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 Acolumn 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 Bcolumn 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.

More About

expand all

Key Variable

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

See Also

|

Was this topic helpful?