_edited.png)
“The Sharing Show” Prism Presentation
I recently had the pleasure of sharing an introduction to Prism example during the inaugural episode of Season 7 of “The Sharing Show”. As promised during the sharing, this blog is the first in a series of spinoffs that will delve deeper into various aspects of Prism.
In this post, I will focus on the Join Type options available in the Join transformation. Joining data involves combining two or more datasets based on a shared attribute or key. In Workday Prism, there are four types of joins you can use:
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Let's explore these options in detail.
Join Types
Inner Join
An inner join combines two tables and shows only the rows that have matching values in both tables. If there’s no match, those rows are not included in the results.
In this example, we have four customers in the Customers table (that's our left table) and three of them have placed orders in the Orders table (the right table). We’re connecting the two tables through the Customer ID, which is the common piece of info that helps us match up the customers with their orders.
David, Customer 4, isn’t in the inner join table because he hasn’t placed any orders. Without a matching Customer ID in the Orders table, he’s not included this time!
Left Outer Join
A left outer join is a way to combine two tables where you get all the records from the left table and only the matching records from the right table. If there’s no match, you still get the left table’s record, but with empty values for the right table’s data.
Similar to the previous example, we have four customers in the Customers table (our left table), with three of them having placed orders in the Orders table (the right table). Once again, we’ll connect these two tables using the Customer ID, allowing us to effectively link customers to their corresponding orders.
David has still not placed any orders; however, this time he is included in the left outer join table, which features only his customer information without any associated order details.
Right Outer Join
A right out join combines two tables and shows all the rows from the right table and only the matching rows from the left table. If there’s no match, you still get the right table’s row, but with empty values for the left table’s data.
In this scenario, we have four customers listed in the Customers table (our left table), of which three have placed orders in the Orders table (the right table). Interestingly, Customer ID 5 is present in the Orders table but is absent from the Customers table. Although such a situation is unlikely in real-world applications, it effectively illustrates the concept of a right outer join. As before, we connect these two tables using the Customer ID, enabling us to link customers to their corresponding orders seamlessly.
David still hasn’t placed any orders, and this time he’s been left out of the right outer join table. On the other hand, Customer ID 5 makes an appearance in the right outer join, but without any accompanying customer data. It’s a reminder that the right outer join invites everyone from the Orders table to the party!
Full Outer Join
A full outer join combines two tables and shows all the rows from both tables. It includes matching rows where available, and if there’s no match, it fills in empty values for the missing data from either table.
In this final scenario, the setup for the left and right tables mirrors that of the right outer join. We have four customers in the Customers table (our left table), with three of them having placed orders recorded in the Orders table (the right table). Notably, Customer ID 5 appears only in the Orders table. As in the previous examples, we connect these two tables using the Customer ID.
In a full outer join, everything is on the table! David, who hasn’t placed any orders, is included without any order data, while Customer ID 5 makes an appearance without corresponding customer details. This means we capture the complete picture, showcasing all customers and orders, even when some entries are missing information.
My Prism Join Example
In my Prism Join transformation step, I am utilizing a Left Outer Join to match rows based on the Cost Center ID. This approach ensures that every row from the left table or primary pipeline is returned, regardless of whether there is a corresponding match in the right table.
As a best practice, it's recommended to match rows using IDs instead of text description fields whenever possible, as this enhances the accuracy and efficiency of the join process.
To change the join type in Prism, simply select your preferred option from the dropdown menu under Join Type.
Selecting the fields for your final dataset is straightforward. Just click on the left table (primary pipeline) and/or the right table, then choose the desired fields you wish to include.
 |  |
Conclusion
Joins in Prism are incredibly useful for creating rich datasets by combining data from various sources, adding depth and meaning to your insights. Dive in and experiment to discover the endless possibilities! And don’t forget to stay tuned for more exciting Prism blogs coming your way!
Author: Christian from Maryland
