`outerjoin`

Joins two tables keeping rows that have no match.

Category: Language Fundamentals > Data Types

Syntax

C = outerjoin(A, B)

Joins on the variables the two tables share, keeping unmatched rows from both.

C = outerjoin(A, B, 'Keys', keys)

Joins on the named key variables.

C = outerjoin(___, 'Type', t)

t is 'full' (default), 'left' or 'right'.

C = outerjoin(___, 'MergeKeys', true)

Merges the key columns of A and B into one.

[C, ia, ib] = outerjoin(___)

Also returns the row indices taken from A and B.

Description

An outer join is the union of the matches and the leftovers: every row of the chosen side appears once, paired with the matching row of the other table if there is one. Unlike innerjoin, which drops unmatched rows, outerjoin pads them with missing values.

Without 'MergeKeys' the key columns stay separate, with suffixes taken from the names of the input tables, so the result shows both keys side by side. Rows come out sorted by key.

Input arguments

A, B: Tables or timetables.
'Keys': Variable name(s) or indices used to match rows.
'Type': Which unmatched rows to keep.
'MergeKeys': If true, a single key variable holds the key from whichever table has it.

Output arguments

C: Joined table. Where a row has no partner, the other table's variables hold missing values (NaN, <missing>).
ia, ib: Column vectors of row indices into A and B; 0 marks rows with no source.

Example

a = table([1;2], ["x";"y"], 'VariableNames', {'K','V'});
b = table([2;3], ["p";"q"], 'VariableNames', {'K','W'});
outerjoin(a, b, 'Keys', 'K', 'MergeKeys', true)

Output

ans = 3×3 table

    K        V            W    
    _    _________    _________

    1    "x"          <missing>
    2    "y"          "p"      
    3    <missing>    "q"

outerjoin