table

Methods

⌂ table

Method Reference: table.innerjoin

table: tbl = innerjoin (tblL, tblR)
table: tbl = innerjoin (tblL, tblR, Name, Value)
table: [tbl, ixL, ixR] = innerjoin (…)

Inner join between two tables by rows using key variables.

tbl = innerjoin (tblL, tblR) combines the tables tblL and tblR by matching the values of their key variables, which by default are the variables that share the same name in both tables. Each row of tbl is formed by horizontally concatenating a row of tblL with a row of tblR whose key variables share the same combination of values. If m rows in tblL and n rows in tblR share the same key combination, then tbl contains all m×n pairings for that combination. The rows of tbl are sorted by the values of the key variables, and any row names are dropped.

By default tbl contains all the variables of tblL followed by the non-key variables of tblR. Whenever a non-key variable name appears in both tables, a suffix derived from each input’s argument name is appended to the conflicting names in tbl (for inputs named tblL and tblR, the suffixes '_tblL' and '_tblR'; when an input has no name, '_left' and '_right' are used).

tbl = innerjoin (tblL, tblR, Name, Value) customizes the join with the following options:

'Keys'
Variables to use as keys in both tables, given as variable names or indices. It cannot be combined with 'LeftKeys' or 'RightKeys'.
'LeftKeys', 'RightKeys'
Variables to use as keys in tblL and tblR, respectively, when the key variables have different names. They must be specified together and reference the same number of variables.
'LeftVariables', 'RightVariables'
Variables of tblL and tblR to include in tbl. They may include or exclude key variables. By default 'LeftVariables' is all the variables of tblL and 'RightVariables' is the non-key variables of tblR.

[tbl, ixL, ixR] = innerjoin (…) also returns the row-index vectors ixL and ixR such that tbl is the horizontal concatenation of tblL(ixL, leftVars) and tblR(ixR, rightVars).

Source Code: table

Example: 1

innerjoin keeps only rows whose key exists in both tables — unmatched rows are dropped — and, unlike join, the right key need not be unique. Pass 'Keys' to match on several variables at once; here a row must agree on both Name and Year to match.

 L = table ({'Li'; 'Li'; 'Diaz'}, [2023; 2024; 2023], [10; 11; 12], ...
            'VariableNames', {'Name', 'Year', 'Visits'})
L =
  3x3 table

      Name      Year    Visits    
    ________    ____    ______    

    {'Li'  }    2023        10    
    {'Li'  }    2024        11    
    {'Diaz'}    2023        12
 R = table ({'Li'; 'Diaz'}, [2023; 2023], categorical ({'NY'; 'CA'}), ...
            'VariableNames', {'Name', 'Year', 'State'})
R =
  2x3 table

      Name      Year    State    
    ________    ____    _____    

    {'Li'  }    2023       NY    
    {'Diaz'}    2023       CA
 innerjoin (L, R, 'Keys', {'Name', 'Year'})
ans =
  2x4 table

      Name      Year    Visits    State    
    ________    ____    ______    _____    

    {'Diaz'}    2023        12       CA    
    {'Li'  }    2023        10       NY

Li's 2024 row has no match on the right, so it is excluded. Extra outputs give the source row indices in the left and right tables.

 [T, iL, iR] = innerjoin (L, R, 'Keys', {'Name', 'Year'});
 [iL, iR]
ans =

   3   2
   1   1