Join sources

A Join is used to combine rows from two or more source datastores, based on an expression that relates columns from these datastores.

Create a join between two datastores

To combine data from two datastores with a join:

  1. Drag and drop the column from the first datastore onto the corresponding column in the second datastore.

  2. A menu opens to select an action. Select Join.

    link types

  3. A join iconjoin is added to the diagram between the two datastores.

    When a join is created between two datastores, a blue area gathering the joined datastores appears. This area represents a dataset.
    If a join already exists between two datastores, drag-and-dropping a column between these datastores adds it to the existing join expression. If you want to create a new join between those datastores, hold the Ctrl key pressed while dropping the column.

  4. In the Expression Editor view, review, and edit the join expression of the join.

    A join expression must use the language and functions from the engine that will process it, which depends on the join’s Execution Location property (Source or Staging Area).

    Think of a join expression as a conditional expression from the WHERE clause of a SQL query.

    Examples of valid join expressions:

    • CUSTOMER.COUNTRY_ID = COUNTRY.COUNTRY_ID

    • UCASE(CUSTOMER.COUNTRYID) = GEOGRAPHY.COUNTRY_ID AND UCASE(CUSTOMER.REGIONID) = GEOGRAPHY.REGION_ID

  5. Select the join in the Mapping diagram.

  6. In the Properties view, set the following Standard Properties:

    • Enable: Enables or disables the join.

    • Set the Join Type:

      • Inner: Performs an Inner Join.

      • Cross: Performs a Cross Join.

      • Full: Performs a Full Outer Join.

      • Or select the Left Part or Right Part radio button to perform respectively a Left Outer or Right Outer join.

        To select the Join Type directly from a mapping diagram, right-click the join on the diagram and then select Join Type > Inner|Left|Right|Full|Cross.
        See Join (SQL) for a definition of join types in SQL.
        Cross and Full joins may produce significant volumes of data.

    • Set the Execution Location. The join may be executed within the Source (when joining two datastores from the same data server) or in the Staging Area.

      To set the Execution Location property directly from the Mapping diagram: select the join on the diagram and then click the source icon to execute the join on the source or the staging icon to execute it in the Staging Area.

    • Description: Free form text.

  7. In the Properties view, optionally set the following Advanced Properties:

    • join Mode: Defines how the join will be generated:

      • Explicit uses the ISO Syntax (join in the FROM clause).

      • Implicit inserts the join in the WHERE clause.

      • Default uses the default type defined for the technology. This latter option is preferred for non-Inner Joins.

        To select the join Mode directly from a Mapping diagram, right-click the join on the diagram and then select Join Mode > Default|Explicit|Implicit.

    • Order: Defines the order of the join when using the Explicit Join Type.

    • Activate: see Conditional joins.

  8. Press Ctrl+S to save the mapping.

Conditional joins

A Conditional Join allows activating a dataset and its corresponding join only if the driving dataset is used.

To define a Conditional Join:

  1. Select the join on the Mapping diagram.

  2. In the Properties view, open the Advanced Properties finger tab.

  3. Set the Activate property:

    • Always: The join is not conditional and will always be executed.

    • With <datastore name>'s dataset: The Conditional join will be activated only if the dataset containing the <datastore name> datastore is used.

Conditional Joins can be particularly useful to mutualize loads inside a Mapping.