This is another option when joining two Data Extensions in the relationship pop-up modal:
This type of join is used to generate a paired combination of each row of the first data extension with each row of the second data extension. This type of join is also referred to as a Cartesian join. The number of records as a result of a Cross Join is the number of records in Data Extension A multiplied by the number of records in Data Extension B.
How does a Cross Join work?
Say we are attending a tournament and we know that the top 3 winners will be awarded a prize. There’s a table representing the possible winner standings:
And in another table we have all the participants in the tournament
If we want to make a list of all possible outcomes of who will win and in what place they will finish, we have to make a list of all the possible combinations of standings and participants. We are therefore going to perform a Cross Join and the table below shows the final result from a Cross Join. So for our example here the total number of records/rows in the Cross Join results will be 12 (3 x 4).
|All possible winners in all possible standings|
What would be a good use case for this in the marketing world?
What if you want to do a test send to validate the correctness of the different campaign versions you are sending? Just perform a Cross Join between an Internal Data Extension containing your team members and Campaign Version Data Extension and you have your list ready to test out all the different iterations of your campaign messages. Beforehand, you could first generate test sends, and make sure that everyone in the DE receives each version for validation purposes before the actual send-out.
When using a Cross Join
- There’s no field matching required in this type of a join as it will create combinations between each row in both Data Extensions
- It is recommended to not use more than one Cross Join in a selection as running such a selection may result in inconsistency in the number of records returned
- If you are only mapping one primary key, the following warning will pop-up
“You only have one primary key mapped for Cross Join. This may result in unexpected results due to primary key constraints. Please remove the primary key or add a primary key from the second Data Extension in the Cross Join.”
Related article: Are JOINs supported in DESelect?