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

OrderIDCustomerIDOrderDate
1030821996-09-18
10309371996-09-19
10310771996-09-20


Second Table


CustomerIDCustomerNameCountry
1AlfredGermany
2Ana TrujilloMexico
3Antonio MorenoMexico

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