Left Join Processor

Modified on Tue, 30 Nov 2021 at 01:09 PM

Overview

This processor executes a left join on two input Datasets by one matching column. Columns selected as join partners must have same data types. All other columns in the two Datasets may not have identical names, the left table is the leading table.

LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

Left join syntax

SELECT column_name(s) 
FROM table1 
LEFT JOIN table2 
ON table1.column_name = table2.column_name;

Input

The processor requires two input Datasets having one attribute column in common. The join operation will be based on this common column. The left input port in the processor corresponds to the displayed Dataset.

Configuration

Output

The result contains columns from both the first and the second input Datasets. It will include all the entries from the first Dataset and the matching ones from the second.

Note that in case of a mismatch, a default value can be defined using the last configuration filed.

Example

In the following example we join two custom input tables using the left join processor.

Workflow

Input tables

First Table

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Second Table

CustomerID	CustomerName	Country
1	Alfred	Germany
2	Ana Trujillo	Mexico
3	Antonio Moreno	Mexico

Note here that not all the customer IDs in the second table are available in the first one.

Example Configuration

Result

As mentioned before, the left table was fully displayed, and corresponding entries from the right table were added.

The data in the added columns having no corresponding values in the second table is left empty.

Relevant Articles

Inner Join Processor