MathWorks Machine Translation
The automated translation of this page is provided by a general purpose third party translator tool.
MathWorks does not warrant, and disclaims all liability for, the accuracy, suitability, or fitness for purpose of the translation.
Outer join between two tables or two timetables
creates
the table or timetable, C
= outerjoin(A,B
)C
, as the outer 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 outer join between
a timetable and a table.
The outer join includes the rows that match between A
and B
,
and also unmatched rows from either A
or B
,
all with respect to the key variables. C
contains
all variables from both A
and B
,
including the key variables.
If A
and B
are timetables,
then the key variables are the vectors of row times of A
and B
.
performs
the outerjoin operation with additional options specified by one
or more C
= outerjoin(A,B
,Name,Value
)Name,Value
pair arguments.
Create a table, A
.
A = table([5;12;23;2;15;6],... {'cheerios';'pizza';'salmon';'oreos';'lobster';'pizza'},... 'VariableNames',{'Age','FavoriteFood'},... 'RowNames',{'Amy','Bobby','Holly','Harry','Marty','Sally'})
A = Age FavoriteFood ___ ____________ Amy 5 'cheerios' Bobby 12 'pizza' Holly 23 'salmon' Harry 2 'oreos' Marty 15 'lobster' Sally 6 'pizza'
Create a table, B
, with one variable in common with A
, called FavoriteFood
.
B = table({'cheerios';'oreos';'pizza';'salmon';'cake'},... [110;160;140;367;243],... {'A';'D';'B';'B';'C'},... 'VariableNames',{'FavoriteFood','Calories','NutritionGrade'})
B = FavoriteFood Calories NutritionGrade ____________ ________ ______________ 'cheerios' 110 'A' 'oreos' 160 'D' 'pizza' 140 'B' 'salmon' 367 'B' 'cake' 243 'C'
Use the outerjoin
function to create a new table, C
, with data from tables A
and B
.
C = outerjoin(A,B)
C = Age FavoriteFood_A FavoriteFood_B Calories NutritionGrade ___ ______________ ______________ ________ ______________ NaN '' 'cake' 243 'C' 5 'cheerios' 'cheerios' 110 'A' 15 'lobster' '' NaN '' 2 'oreos' 'oreos' 160 'D' 12 'pizza' 'pizza' 140 'B' 6 'pizza' 'pizza' 140 'B' 23 'salmon' 'salmon' 367 'B'
Table C
contains a separate variable for the key variable from A
, called FavoriteFood_A
, and the key variable from B
, called FavoriteFood_B
.
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 outerjoin
function to create a new table, C
, with data from tables A
and B
. Merge the key values into a single variable in the output table, C
.
C = outerjoin(A,B,'MergeKeys',true)
C = Key1 Var1 Var2 ____ ____ ____ 'a' 1 4 'b' 2 5 'c' 3 NaN 'd' NaN 6 'e' 11 7 'h' 17 NaN
Variables in table C
that came from A
contain null values in the rows that have no match from B
. Similarly, variables in C
that came from B
contain null values in those rows that had no match from A
.
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 outerjoin
function to create a new table, C
, with data from tables A
and B
. Match up rows with common values in the key variable, Key1
, but also retain rows whose key values don't have a 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] = outerjoin(A,B)
C = Key1_A Var1 Key1_B Var2 ______ ____ ______ ____ 'a' 1 'a' 4 'b' 2 'b' 5 'c' 3 '' NaN '' NaN 'd' 6 'e' 11 'e' 7 'h' 17 '' NaN ia = 1 2 3 0 4 5 ib = 1 2 0 3 4 0
The index vectors ia
and ib
contain zeros to indicate the rows in table C
that do not correspond to rows in tables A
or B
, respectively.
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 outerjoin
function to create a new table, C
, with data from tables A
and B
. Ignore rows in B
whose key values do not match any rows in A
.
Also, return index vectors, ia
and ib
indicating the correspondence between rows in C
and rows in A
and B
respectively.
[C,ia,ib] = outerjoin(A,B,'Type','left')
C = Key1_A Var1 Key1_B Var2 ______ ____ ______ ____ 'a' 1 'a' 4 'b' 2 'b' 5 'c' 3 '' NaN 'e' 11 'e' 7 'h' 17 '' NaN ia = 1 2 3 4 5 ib = 1 2 0 4 0
All values of ia
are nonzero indicating that all rows in C
have corresponding rows in A
.
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 outer join. C
matches up the rows with common row times, but also includes the rows that do not have matches.
C = outerjoin(A,B)
C = Time Var1_A Var1_B _____ ______ ______ 1 sec 1 NaN 2 sec 2 4 4 sec 3 5 6 sec 11 6 7 sec NaN 7
Combine A
and B
, but ignore rows in B
whose row times do not match any row times in A
.
D = outerjoin(A,B,'Type','left')
D = Time Var1_A Var1_B _____ ______ ______ 1 sec 1 NaN 2 sec 2 4 4 sec 3 5 6 sec 11 6
A,B
— Input tablesInput tables, specified as tables or as timetables.
Specify optional commaseparated 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
.
'Keys',2
uses the second variable
in A
and the second variable in B
as
key variables.'Keys'
— Variables to use as keysVariables to use as keys, specified as the commaseparated 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'
namevalue pair
argument with the 'LeftKeys'
and 'RightKeys'
namevalue
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.
'LeftKeys'
— Variables to use as keys in A
Variables to use as keys in A
, specified
as the commaseparated 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'
namevalue pair
argument in conjunction with the 'RightKeys'
namevalue
pair argument. 'LeftKeys'
and 'RightKeys'
both
must specify the same number of key variables. outerjoin
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.
'RightKeys'
— Variables to use as keys in B
Variables to use as keys in B
, specified
as the commaseparated 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'
namevalue pair
argument in conjunction with the 'LeftKeys'
namevalue
pair argument. 'LeftKeys'
and 'RightKeys'
both
must specify the same number of key variables. outerjoin
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.
'MergeKeys'
— Merge keys flagfalse
(default)  true
 0
 1
Merge keys flag, specified as the commaseparated pair consisting
of 'MergeKeys'
and either false
, true
, 0
or 1
.

This is the default behavior. 


'LeftVariables'
— Variables from A
to include in C
Variables from A
to include in C
,
specified as the commaseparated 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, outerjoin
includes all variables
from A
.
'RightVariables'
— Variables from B
to include in C
Variables from B
to include in C
,
specified as the commaseparated 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, outerjoin
includes all the variables
from B
.
'Type'
— Type of outer join operation'full'
(default)  'left'
 'right'
Type of outerjoin operation, specified as the commaseparated
pair consisting of 'Type'
and either 'full'
, 'left'
,
or 'right'
.
For a left outer join, C
contains
rows corresponding to key values in A
that do not
match any values in B
, but not viceversa.
For a right outer join, C
contains
rows corresponding to key values in B
that do not
match any values in A
, but not viceversa.
By default, outerjoin
does a full outer join
and includes unmatched rows from both A
and B
.
C
— Outer join from A
and B
Outer 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 key values. If A
and B
contain
variables with the same name, outerjoin
adds a
unique suffix to the corresponding variable names in C
.
Variables in C
that came from A
contain
null values in those rows that had no match from B
.
Similarly, variables in C
that came from B
contain
null values in those rows that had no match from A
.
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
also contains rows corresponding
to key value combinations in one input table that do not match any
row the other input table.
C
contains the horizontal concatenation of A(ia,LeftVars)
and B(ib,RightVars)
sorted
by the values in the key variables. By default, LeftVars
consists
of all the variables of A
, and RightVars
consists
of all the from B
. Otherwise, LefttVars
consists
of the variables specified by the 'LeftVariables'
namevalue
pair argument, and RightVars
consists of the variables
specified by the 'RightVariables'
namevalue pair
argument.
You can store additional metadata such as descriptions, variable
units, variable names, and row names in the table. For more information,
see Table Properties
.
ia
— Index to A
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
.
The vector ia
contains zeros to indicate the rows
in C
that do not correspond to rows in A
.
ib
— Index to B
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
.
The vector ib
contains zeros to indicate the rows
in C
that do not correspond to rows in B
.
Variable used to match and combine data between
the input tables, A
and B
.
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
You can also select a location from the following list: