Documentation

This is machine translation

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

innerjoin

Inner join between two tables or two timetables

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 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. 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) 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 = 

    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     

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.

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.

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

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.

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.

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

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.

Tall Array Support

This function fully supports tall arrays. For more information, see Tall Arrays.

See Also

|

Introduced in R2013b

Was this topic helpful?