# Join Transformation

The *Join* transformation object joins records from two record sets. The join functionality is similar to standard SQL joins, but the distinguishing advantage of Astera's implementation is that you can join records from any two sources and not just two database tables.

This article covers how you can use *Join* transformation in Astera.

{% embed url="<https://youtu.be/2J8s9h5JLjs>" %}

## **Sample Use Case**

Suppose we have two database tables - *Customers* and *Orders*, as shown in the screenshot below, and we want to join these two tables.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FzNnwQlNadgfRXxsglYgS%2Fimage.png?alt=media&#x26;token=66d8fb38-9bce-44b8-9db3-a332b351a345" alt=""><figcaption></figcaption></figure>

## Working with the Join Transformation Object

Let’s see how we can join the two tables using the *Join* transformation object in Astera:

1. Drag and drop the *Join* transformation object from the *Transformations* section in the Toolbox. To open the Toolbox, go to *View > Toolbox*.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FkyF3NMA56amSlIn56ieO%2Fimage.png?alt=media&#x26;token=7bf1c8fe-68a9-4a18-bea3-c03211d1c615" alt=""><figcaption></figcaption></figure>

2. Map the fields from the source objects to the *Join* transformation object.

{% hint style="info" %}
**Note:** To quickly add fields to the layout, drag and drop the bold node’s output port of the object whose layout you wish to replicate to the bold *Join* node of the Join object.
{% endhint %}

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FE7TgBYExjA79wTdGwsAn%2Fimage.png?alt=media&#x26;token=9955cb9a-25cd-49e0-85b0-4159f0456d20" alt=""><figcaption></figcaption></figure>

3. To set the properties for the *Join* transformation, double-click on the object or right-click and select *Properties*.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FIz25h1ZSssrLch2uifrF%2Fimage.png?alt=media&#x26;token=3dd10606-2705-4a94-b799-e522de54b997" alt=""><figcaption></figcaption></figure>

4. The first window is the *Layout Builder* window. You can manage the layout for your transformation (add or remove fields) from this window. Click *Next* to go to the next window.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2F6zWjvGMOMaNKHAjI3gkP%2Fimage.png?alt=media&#x26;token=bdf7aa0c-2d40-4f68-8848-10f6523d9dd3" alt=""><figcaption></figcaption></figure>

5. The next window is the *Relation Join Transformation Properties* window. Select the *Join Type* from the drop-down menu. Astera supports four types of joins:
   * *Inner Join* – Joins records from two record sets based on matching values in key fields. Any unmatched records are discarded.
   * *Left Outer Join* – Similar to *Inner Join, however,* unmatched records from the left record set (also called ‘first record set’) are preserved, and null values are written for the unmatched record in the right record set (also called ‘second record set’).
   * *Right Outer Join* – Similar to *Inner Join*, *however,* unmatched records from the right record set (also called ‘second record set’) are preserved, and null values are written for the unmatched record in the left record set (also called ‘first record set’).
   * *Full Outer Join* - similar to *Inner Join*, *however,* unmatched records from either record set are preserved, and null values are written for the unmatched record in the other record set.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FsSzNyM2jYpnUAFowR7Lc%2Fimage.png?alt=media&#x26;token=537f1cf3-b5d0-4564-acc3-d921eada6aec" alt=""><figcaption></figcaption></figure>

6. Other options in this window:
   * *Join in Database*: Check this option if you want to join the tables in the database.
   * *Case Sensitivity*: Check this option if you want a case-sensitive match of the values in the key fields.
   * *Sort (Left/Right) Input*: Specify whether the left input, the right input, or both, need to be sorted.
7. Select the key fields from the *Left Field* and *Right Field* drop-down lists. Click *Next*, then *OK*.

{% hint style="info" %}
**Note:** You can add multiple fields to create a composite key field.
{% endhint %}

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FZkF1EDpKTiao763Ur9zS%2Fimage.png?alt=media&#x26;token=5fe1785d-c758-4013-b76b-e6478e0181c0" alt=""><figcaption></figcaption></figure>

8. You can now preview the output and see the consolidated data.

<figure><img src="https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FlogvTYQ1MAh7saiZ7OSf%2Fimage.png?alt=media&#x26;token=5cdba2a0-f007-4113-880a-f8c56a00ade3" alt=""><figcaption></figcaption></figure>

## ***General Options*****&#x20;Window**

This window consists of options common to most objects in a dataflow:

* *General Options* Window: This window shares options common to most objects in the dataflow.
* *Clear Incoming Record Messages:* When this option is checked, any messages coming in from objects preceding the current object will be cleared. This is useful when you need to capture record messages in the log generated by the current object and filter out any record messages generated earlier in the dataflow.
* *Do Not Process Records with Errors:* When this option is checked, records with errors will not be output by the object. When this option is off, records with errors will be output by the object, and a record message will be attached to the record. This record message can then be fed into downstream objects in the dataflow, for example, a destination file that will capture record messages or a log that will capture messages and collect statistics as well.
* The *Comments* input allows you to enter comments associated with this object.