Merge Join

Standard SQL joins ..

A guided demo to illustrate the various SQL joins.

In this guided demonstration, you will:

• Configure the following steps:

  • Merge Join

Merge Join

1. Data Grids

The Data Grid step allows you to enter a static list of rows in a grid. This is usually done for testing, reference or demo purposes.

Options

• Meta tab: You can specify the field metadata (output specification) of the data

• Data tab: This grid contains the data. Everything is entered in String format so make sure you use the correct format masks in the metadata tab.

1. Drag the Data Grid step onto the canvas.

2. Open the Data Grid properties dialog box.

3. Ensure the following details are configured, as outlined below:

ABCD - Data Grid

Red Blue Yellow - data grid

2. Merge Join

The Merge Join step performs a classic merge join between data sets with data coming from two different input steps. Join options include INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER.

1. Drag the Merge Join step onto the canvas.

2. Open the Merge Join properties dialog box.

3. Select various Join Types to view the resulting dataset

Merge Join

Obviously you need to join on a unique key(s)

3. RUN

1. Click the Run button in the Canvas Toolbar.

2. Click on the Dummy step Preview tab:

INNER Join

INNER Join

LEFT OUTER Join

LEFT OUTER Join

RIGHT OUTER Join

RIGHT OUTER Join

FULL OUTER Join

FULL OUTER Join

Now give it a go with the 'Merge Streams' scenario ..