Main Content

Join Tables

Combine two tables using key variables in the Live Editor

Since R2019b

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.

Join Tables task in the Live Editor

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 or tables. Select Join Tables from the suggested command completions.

Examples

expand all

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:

  1. Select orders and items as the left and right tables, respectively.

  2. Select OrderID as the merging variable for both tables.

  3. Click the Inner join button.

  4. To see the code that this task generates, expand the task display by clicking Show code at the bottom of the task parameter area.

Live Task
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 and 5120 occur in both input tables. Therefore, the output table contains data for only those two orders.

  • Value 1037 occurs multiple times in items, once for each item, but only once in orders. Therefore, the CustomerID and OrderDate values corresponding to 1037 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.

  1. Click the Left outer join button.

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

  3. To see the code that this task generates, expand the task display by clicking Show code at the bottom of the task parameter area.

Live Task
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

expand all

Specify the name from a list of all the nonempty tables and timetables that are in the workspace.

Specify the name from a list of all the nonempty tables and timetables that are in the workspace.

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

expand all