What is a LEFT OUTER JOIN

Left outer join

  • 3 minutes to read

One of the dialog boxes together to lead join types available in Power Query is a left outer join, which retains all rows from the table on the left and returns all matching rows from the table on the right. More information: Overview of collaboration processes

The figure shows a table on the left with the columns Date, Countryid and Units. The highlighted countryid column contains the values ​​1 in rows 1 and 2, 3 in row 3 and 4 in row 4. A table on the right has ID and country columns. The highlighted ID column contains the values ​​1 in line 1 (denotes USA), 2 in line 2 (in the denomination Canada) and 3 in line 3 (for Panama). A table below the first two tables contains the columns "Date", "countryid", "Units" and "Country". The table contains four lines with the top two lines containing the data for countryid 1, one line for countryid 3 and one line for country ID 4. Since the right table did not contain ID 4, the value of the fourth line contains in the Country column has the value NULL.

This article uses sample data to demonstrate how to merge the left outer join. The sample source tables for this example are as follows:

  • Sales: This table contains the fields Date, countryidand Units. Countryid is an integer value that is the unique identifier from the countries Table represents.

    ! [Sales table with the columns Date, countryid and Units, where countryid is set to 1 in rows 1 and 2, 3 in row 3 and 4 in row 4.] (images / me-merge-operations-left-outer -join-sales-table.png "Sales table with the columns Date, Countryid and Units, where countryid is set to 1 in rows 1 and 2, 3 in row 3 and 4 in row 4.")

  • countries: This table is a reference table with the fields " ID " and " Country". The ID Field represents the unique identifier for each data record.

    The figure shows a table on the left with the columns Date, Countryid and Units. The highlighted countryid column contains the values ​​1 in rows 1 and 2, 3 in row 3 and 4 in row 4. A table on the right has ID and country columns. The highlighted ID column contains the values ​​1 in line 1 (denotes USA), 2 in line 2 (in the denomination Canada) and 3 in line 3 (for Panama). A table below the first two tables contains the columns "Date", "countryid", "Units" and "Country". The table contains four lines with the top two lines containing the data for countryid 1, one line for countryid 3 and one line for country ID 4. Since the right table did not contain ID 4, the value of the fourth line contains in the Country column has the value NULL.

This article uses sample data to demonstrate how to merge the left outer join. The sample source tables for this example are as follows:

  • Sales: This table contains the fields Date, countryidand Units. Countryid is an integer value that is the unique identifier from the countries Table represents.

    ! [Sales table with the columns Date, countryid and Units, where countryid is set to 1 in rows 1 and 2, 3 in row 3 and 4 in row 4.] (images / me-merge-operations-left-outer -join-sales-table.png "Sales table with the columns Date, countryid and Units, where countryid is set to 1 in rows 1 and 2, 3 in row 3 and 4 in row 4.")

  • countries: This table is a reference table with the fields " ID "and" with the results of the previous left outer join procedure ")

In the Newly Created column, expand countries the field country . Don't check the box use original column name as prefix .

After you've done this process, create a table that looks like the following image.

Is this page helpful?