---
description: Good old Cartesian Join ..
---

# Cross Join

{% hint style="warning" %}
#### Workshop - Cross Join

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement ..  whatever that means .. basically, its every possible combination.

In this workshop we'll be cross joining first names with middle names and again with our surname.
{% endhint %}



<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/zr4G4h8dJ8zC3sNkKDwX/cartesian.png" alt=""><figcaption><p>Cross / Cartesian Join</p></figcaption></figure>

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/hpLC2beVxWbqv7dSrwRf/name.png" alt=""><figcaption><p>Cross Joins</p></figcaption></figure>

***

{% tabs %}
{% tab title="English" %}
<figure><img src="https://155189258-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F7IROMjOTNySvPnpnJs27%2Fuploads%2FHEbLeBigP3mA83eoxRXf%2Fcross_join.png?alt=media&#x26;token=82c1570a-16f6-40ff-bbf0-b2798ecdcffa" alt=""><figcaption></figcaption></figure>

{% hint style="success" %}
_The following content has been automatically generated by an AI system and should be used for informational purposes only. We cannot guarantee the accuracy, completeness, or timeliness of the information provided._

_Any actions taken based on this content are at your own risk. We recommend seeking qualified expertise or conducting further research to validate and supplement the information provided._
{% endhint %}
{% endtab %}

{% tab title="Second Tab" %}

{% endtab %}
{% endtabs %}

{% hint style="info" %}
#### Create a new Transformation

Any one of these actions opens a new Transformation tab for you to begin designing your transformation.

* By clicking File > New > Transformation
* By using the CTRL-N hot key
{% endhint %}

{% tabs %}
{% tab title="1. Data Grid" %}
{% hint style="info" %}
You should be familiar with the Data Grid step. Used to list values for first and middle names.
{% endhint %}

<div><figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/OgIpmuabX8s9ScMCeDUm/DG%20-%20first%20name.png" alt=""><figcaption><p>First names</p></figcaption></figure> <figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/fEoL8kjOWGzJre8NgJF4/DG%20-%20middle%20name.png" alt=""><figcaption><p>Middle names</p></figcaption></figure></div>
{% endtab %}

{% tab title="2. Join" %}
{% hint style="info" %}
Joins all possible first\_name, middle\_name combinations together.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/rxDkkc4365e1eHDOhjiv/Join%20rows.png" alt="" width="375"><figcaption><p>Join rows</p></figcaption></figure>

{% hint style="info" %}
You can also add a condition to constrain the resulting dataset.
{% endhint %}
{% endtab %}

{% tab title="3. UDJE" %}
{% hint style="info" %}
2 new fields are added to the data stream:&#x20;

* first\_middle\_name: concates the first\_names and middle names.&#x20;
* initials: returns the 2 character initials.&#x20;

This method has two variants and returns a new string that is a substring of this string. The substring begins with the character at the specified index and extends to the end of this string or up to endIndex – 1, if the second argument is given.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/1ne6BXLSn159muAaWgmS/UDJE%20-%20concat%20names.png" alt="" width="563"><figcaption><p>UDJE - concat names, initials</p></figcaption></figure>
{% endtab %}

{% tab title="4. Get variable" %}
{% hint style="info" %}
Returns the value associated with the ${surname}. This is set in the Parameters tab in Transformation properties.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/jvnbyEtyixitpNzwYicm/Param%20-%20surname.png" alt="" width="563"><figcaption></figcaption></figure>
{% endtab %}

{% tab title="5. Join" %}
{% hint style="info" %}
Joins all possible first\_middle\_name, surname combinations together. The output for initials is also excludes in the list various initials combinations.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/V3Rr4qrilWXZnvU9Vi5z/Join%20rows%20-%20surname.png" alt="" width="563"><figcaption><p>Join rows</p></figcaption></figure>
{% endtab %}

{% tab title="6. Select values" %}
{% hint style="info" %}
Determine the order and selection of the data stream fields.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/P0w6bJUGvT9ofWf0LnmF/Select%20values%20-%20order%20fields.png" alt="" width="563"><figcaption><p>Select values</p></figcaption></figure>
{% endtab %}

{% tab title="7. UDJE" %}
{% hint style="info" %}
2 new fields are added to the data stream:&#x20;

* boys\_initials: returns the babys’ 3 character initials.&#x20;
* boys\_name: concates first\_name + middle\_name + surname
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/l4ZPVioeSMSQF8klaGCq/UDJE%20-%20name.png" alt=""><figcaption><p>UDJE - name</p></figcaption></figure>
{% endtab %}

{% tab title="8. Reservoir Sampling" %}
{% hint style="info" %}
* Returns 5 sampled records.&#x20;
* Returns Random seed ${seed}&#x20;
* Reservoir Sampling allows you to select a set number of random records, from an unknown number ‘reservoir’ of records, i.e. not known beforehand.&#x20;
* Use a different seed value to ensure no two ‘sets’ are the same.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/7p7W5kKXDk13JC2K5Cpo/Res%20sampling.png" alt="" width="375"><figcaption><p>Reservoir sampling</p></figcaption></figure>
{% endtab %}

{% tab title="9. RUN" %}
{% hint style="info" %}
#### RUN

The workshop illustrates the use of cross joins to create data sets with every possible combination - unless conditions are set.  The final dataset is randomly selected using Reservoir Sampling - a common technique used in ML.
{% endhint %}

1. Click the Run button in the Canvas Toolbar.
2. Click on the Preview tab:

<figure><img src="https://content.gitbook.com/content/7IROMjOTNySvPnpnJs27/blobs/qBRdCPxp8yTSdVjQZluW/Results%20-%20cross%20join.png" alt=""><figcaption><p>Preview data</p></figcaption></figure>
{% endtab %}
{% endtabs %}
