N-way joins allows you to create a join project where multiple tables are joined together for data analysis. Unlike a simple join, a join project allows you to join two real entities at a time to create one or more output entities based on one or more result segments. These output entities can be instantly joined again with other entities to create multi-way joins. Sometimes the joining rows are of interest, but you are more likely to isolate and analyze non-joining rows in preparation for a data cleansing or a migration project.
Here is a typical n-way join:
Just like a simple join, the n-way joins allow you to examine relationships between entities, no matter what the original data source. Venn diagrams and Entity Relationship Diagrams (ERD) are available to help you to better visualize joins.
However, when creating n-way joins, you can specify a join expression for the left entity that may or may not match an expression over the right entity.
In a simple join, for example, the row will join only when the value of customer_id in the customer table (LHS) is the exact same as customer_id in the order table (RHS):
customer.customer_id = order.customer_id
However, join criteria in real life are far more complex and the join project provides you the capability to create n-way joins with:
- Multiple join criteria:
customer.customer_id = order.customer_id and customer.postcode = order.delivery_postcode
- Expression based join criteria -
trim(customer.customer_id) = substring(order.customer_id,1 6) and concat(customer.postcodeleft, customer.postcoderight) = order.delivery_postcode