Nov 18, 2016

Inner / full joins and union / intersection

UPDATE: The Merge transformation has got the "Full Join" mode starting from version 3.5.

As you may have already noticed, there is no "Join" transformation in EasyMorph, but instead there is the "Merge" transformation which can work as left join in one of its modes. We intentionally walked away from the traditional representation of multi-modal joins (inner/left/right/full/cross) because relational joins are counter-intuitive and hard to understand not just for business users, but also many developers struggle to join data correctly. You won't believe how many SQL developers fail simple questions about joins during job interviews.

As of version 3.3 the left join mode is the only join mode in the "Merge" transformation (the other mode is lookup) simply because in vast majority of real-life cases tables have to be joined using a left join. Also having fewer modes to choose from minimizes possible confusion for new users and keeps things simple.  However, it doesn't mean that other types of joins can't be arranged in EasyMorph. Here is how to do it:

Inner join

Inner joins are arranged by simply inserting a "Keep matching" transformation before "Merge". The "Keep matching" transformation should remove rows where key field values don't exist in the other table thus ensuring that merging is made only for key values that exist in both tables.

Inner join (click to zoom)

Full outer join

A full join is arranged as a left join with concatenation of rows from the "right" table with key values that are missing in the "left" table and therefore were not not included in the left join.

Full join (click to zoom)


A union of two tables is straight forward in EasyMorph: remove rows that exist in the other table using "Keep mismatching" transformation, and then append both tables using "Append" transformation.

Union (click to zoom)


Finally, intersection is achieved using only the "Keep matching" transformation.

Intersection (click to zoom)