4.8. Join node

Icon:

• Number of inputs: 2 or more.

• Number of outputs: 1.

Definition

A join node lets you make an SQL join using multiple input datasets.

Configuration

When the join node is connected at input to at least two nodes, you can click Add a join to configure for each pair of input datasets involved in the join:

• The type of join: Inner, Left Outer, Right Outer, Full Outer .

• The pairs of fields to be joined .

• The join condition (=, <>, >, <, >=, <) for each pair of fields .

Example

• Four possible types of join:

  Based on the following data and making the join at the make field:

  Make	Model
  Ford	Focus
  Citroen	Picasso
  Renault	Megane
  Nissan	GT-R

  Make	Country
  Renault	France
  Toyota	Japan
  Ford	USA

  • Inner: keeps data whose records in the first dataset match those in the second dataset:

  Make	Model	Country
  Ford	Focus	USA
  Renault	Megane	France

  • Left (or Left Outer): keeps all the data from the first dataset by matching its records with those of the second dataset. If no match is found, the fields corresponding with the second dataset will be blank:

  Make	Model	Country
  Ford	Focus	USA
  Citroen	Picasso	<null>
  Renault	Megane	France
  Nissan	GT-R	<null>

  • Right (or Right Outer): keeps all the data from the second dataset by matching its records with those of the first dataset. If no match is found, the fields corresponding with the first dataset will be blank:

  Make	Model	Country
  Renault	Megane	France
  Toyota	<null>	Japan
  Ford	Focus	USA

  • Full (or Full Outer): keeps all data from each dataset (first and second) by matching its records with those of the other dataset. If no match is found in any dataset, the corresponding fields will be blank:

  Make	Model	Country
  Ford	Focus	USA
  Citroen	Picasso	<null>
  Renault	Megane	France
  Nissan	GT-R	<null>
  Toyota	<null>	Japan