Join Tables
Combine two tables using key variables in the Live Editor
Description
The Join Tables task lets you interactively combine two tables by performing joins or by concatenating the tables horizontally or vertically. The task automatically generates MATLAB® code for your live script.
Using this task, you can:
Perform joins, including inner and outer joins, on tables by specifying key variables—table variables whose matching values, or key values, determine which rows are merged in the output table.
Horizontally or vertically concatenate tables with the same number of rows or variables, respectively.
Open the Task
To add the Join Tables task to a live script in the MATLAB Editor:
On the Live Editor tab, select Task > Join Tables.
In a code block in the script, type a relevant keyword, such as
join
,table
,innerjoin
, orouterjoin
. SelectJoin Tables
from the suggested command completions. For some keywords, the task automatically updates one or more corresponding parameters.
Examples
Join Tables Using Live Editor Task
Use the Join Tables Live Editor task to perform an inner join and an outer join on two tables.
First, load the orders
table, which has order IDs, customer names, and order dates for a number of shipments.
load orders
orders
orders=3×3 table
OrderID CustomerID OrderDate
_______ __________ ___________
5120 "Sanchez" 23-Apr-2019
1037 "Li" 18-Apr-2019
8937 "Johnson" 16-Apr-2019
Then load the items
table, which contains products that customers ordered, along with the quantity, price, and status of the shipment for that item. Each row of this table has an order ID, just like orders
. Because a customer can order multiple items, several rows of items
can refer to one order from orders
.
load items
items
items=5×5 table
OrderID Product Quantity Price Status
_______ ________________ ________ _____ _________
6005 "Dozen Roses" 1 39.99 Shipped
1037 "Petunia Basket" 1 23.99 Delivered
5120 "Tulips" 12 0.99 Pending
1037 "Gardenias" 1 17.99 Shipped
1037 "Gerber Daisies" 6 1.99 Delivered
Open the Join Tables task. To open the task, type the keyword join
in a code block and select Join Tables
when it appears in the menu.
Use the task to perform an inner join of orders
and items
. When the task opens:
Select
orders
anditems
as the left and right tables, respectively.Select
OrderID
as the merging variable for both tables.Click the Inner join button.
To see the code that this task generates, expand the task display by clicking Show code at the bottom of the task parameter area.
joinedData=4×7 table
OrderID CustomerID OrderDate Product Quantity Price Status
_______ __________ ___________ ________________ ________ _____ _________
1037 "Li" 18-Apr-2019 "Petunia Basket" 1 23.99 Delivered
1037 "Li" 18-Apr-2019 "Gardenias" 1 17.99 Shipped
1037 "Li" 18-Apr-2019 "Gerber Daisies" 6 1.99 Delivered
5120 "Sanchez" 23-Apr-2019 "Tulips" 12 0.99 Pending
When you perform an inner join, the output table includes only those key values that appear in both the left and right tables.
Only the values
1037
and5120
occur in both input tables. Therefore, the output table contains data for only those two orders.Value
1037
occurs multiple times initems
, once for each item, but only once inorders
. Therefore, theCustomerID
andOrderDate
values corresponding to1037
are copied as many times as needed in the output.
Next, use the task to perform a left outer join. Outer joins can include key values that appear in only one input table. For example, a left outer join includes all key values from the left table, even when the right table has no corresponding matches. If the right table has key values that do not have matches in the left table, then those key values are not included.
Click the Left outer join button.
Select the Combine merging variables check box. By default, outer joins copy the key variables from the left and right tables into separate variables in the output table. Merge the key variables so one key variable is in the output.
To see the code that this task generates, expand the task display by clicking Show code at the bottom of the task parameter area.
joinedData2=5×7 table
OrderID CustomerID OrderDate Product Quantity Price Status
_______ __________ ___________ ________________ ________ _____ ___________
1037 "Li" 18-Apr-2019 "Petunia Basket" 1 23.99 Delivered
1037 "Li" 18-Apr-2019 "Gardenias" 1 17.99 Shipped
1037 "Li" 18-Apr-2019 "Gerber Daisies" 6 1.99 Delivered
5120 "Sanchez" 23-Apr-2019 "Tulips" 12 0.99 Pending
8937 "Johnson" 16-Apr-2019 <missing> NaN NaN <undefined>
The output table now includes data for order 8937
. However, because the items
table for order 8937
had no items, the rest of the row is filled in with empty values (such as <missing>
, NaN
, or <undefined>
). Outer joins fill table elements with empty values when the left or right tables do not have data associated with a key value.
Full outer joins include all key values and data from both tables.
Left outer joins include all key values and data from the left table, but only matching key values and associated data from the right.
Right outer joins include all key values and data from the right table, but only matching key values and associated data from the left.
Related Examples
Parameters
Left table
— Name of table or timetable
drop-down list item
Specify the name from a list of all the nonempty tables and timetables that are in the workspace.
Right table
— Name of table or timetable
drop-down list item
Specify the name from a list of all the nonempty tables and timetables that are in the workspace.
Merging variable
— Name of table variable with key values
drop-down list item
Specify the name of a variable from a list of variables in the left or right table.
The drop-down list for the left table contains a full list of its variables.
The drop-down list for the right table contains a subset of variables. The list of merging variables from the right table is constrained by your choice of merging variables from the left table.
When you specify a merging variable, or key variable, its values determine which rows are merged from the left and right tables. To specify multiple sets of merging variables, use the + button.
Combine merging variable
— Option to combine left and right merging variables
off
(default) | on
Combine corresponding merging variables when performing outer joins. By default, outer joins copy key variables from the left and right tables to separate variables in the output table. To combine corresponding key variables in the left and right tables into one variable in the output, select this check box.
Version History
Introduced in R2019bR2023b: Sort output timetable by row times when row times are not key values
When the left input of the Join Tables task in the Live Editor is a timetable, you can sort the output timetable by row times even when you do not specify row times as key values. To sort by row times in this case, select the Sort result by row times check box.
This sorting option is available only when all three of these conditions are true:
The left input is a timetable that is sorted by row times.
You specify an outer join or an inner join.
You do not specify row times as key values.
If you do specify that row times are key values, then the output timetable is automatically sorted by row times.
R2022b: Task automatically selects merging variables based on scoring algorithm
If the Join Tables Live Editor task fails to automatically select the first pair of merging variables based on row labels or variable names, then it tries to select them based on a scoring algorithm described in Auto-Suggest: Learning-to-Recommend Data Preparation Steps Using Data Science Notebooks. The Join Tables task selects and tests candidate pairs of merging variables using these steps:
Select row names (in a table) or row times (in a timetable) as the first pair of merging variables.
If step 1 fails, then select variables with names that exactly match as the first pair.
If steps 1 and 2 fail, then score pairs of variables using the scoring algorithm. Select the pair of variables with the highest score as the first pair of merging variables.
If all previous steps fail, then select the first items in the Merging variable drop-down lists as the first pair of merging variables.
In previous releases, step 3 was to select the pair of variables whose names gave the best partial match as the first pair of merging variables.
R2022a: Live Editor task does not run automatically if inputs have more than 1 million elements
This Live Editor task does not run automatically if the inputs have more than 1 million elements. In previous releases, the task always ran automatically for inputs of any size. If the inputs have a large number of elements, then the code generated by this task can take a noticeable amount of time to run (more than a few seconds).
When a task does not run automatically, the Autorun indicator is disabled. You can either run the task manually when needed or choose to enable the task to run automatically.
Commande MATLAB
Vous avez cliqué sur un lien qui correspond à cette commande MATLAB :
Pour exécuter la commande, saisissez-la dans la fenêtre de commande de MATLAB. Les navigateurs web ne supportent pas les commandes MATLAB.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)