Help and Support > Table of Contents > Join

Join Block


About:

The join block joins two tables together. Its possible to have the result be either common rows, or non-joined rows, or any combination.

Join Block Image

Block Connection Stubs

Inputs
Outputs
  • 1 The data set 1 to be joined.
  • 3,4,5,6 The result of the join.
  • 2 The data set 2 to be joined.

Join Block

The join block is used to create a join between two tables. It allows joins on multiple columns, and can be configured to provide all of the standard join types. (Inner, outer, left right). To illustrate its operation, we'll use two simple text files:

  • Departments.txt - which contains a list of departments in a company
  • Employees.txt - which contains a list of employees in a company

Every employee can belong to one department, and the two tables can be joined together on the DEPT_ID field which is present in both. To do the join, drag and drop a join block onto the canvas containing the two import blocks, and connect one to each input of the join block.

join2-join2-block-connected-to-department-and-employee (71K)

You will see the join blocks configuration area above. The first step is to define which columns are to be used to join the two sets of data together. In this case it is the DEPT_ID field. To create a join, select DEPT_ID in both the Table 1 list box, and the Table 2 list box, then press the "Add Join" button. For tables where you want to join on more than one field, repeat this process as many times as you like. It is not necessary that the two columns have the same name- whichever columns picked will be joined. After you add all the joins you want, press the run button to the left of the Block name on the control to run the block.

join2-dept_id-joined-and-block-run (77K)

Once the block has run, you will notice that the venn diagram to the left will show the number of rows for each of the possible join results. When joining two tables, there are three possible sets of data:

  1. Rows from table 1 that do not join with any rows in table 2. (In this case, departments that do not have any employees)
  2. Rows that are the result of a successful join. (Employee rows joined to department rows.)
  3. Rows from table 2 that do not join with any rows in table 1. (In this case, employees that have a department code that does not match any code in the department table)

Depending on what you are trying to achieve, any one of these sets, or any combination of them may be the desired output for the block. To configure what you want, simply check all the sets that you want included in the output. The venn diagram will show in green which rows will be included.