Hi venkatRao -- the "=" join in Incorta is most commonly used and behaves like a LEFT OUTER join. Different "SQL" join behaviors like INNER and RIGHT OUTER can be accomplished in Incorta by the way filters are applied after data is brought into Incorta (eg. https://community.incorta.com/t/631psf/how-to-do-inner-join-in-incorta). The other join conditions listed above are seldom used but can be helpful in certain situations. They are mostly used when there are multiple join conditions between two tables. For example, the screenshot attached ensures that the join to the Customer table gets the right "time slice" for customer records that might be slowly changing.
Incorta also provides the other variations of joins:
1. You can provide a filter on the parent table
This is useful when the parent table is a table with multiple data sets and only a subset is applicable in the join, but the key used in the join may not be unique across sets.
2. You can use range join as a second criteria
For example, the parent table has a FROM and a TO column and the child table value can find a matching record from the parent with the range. If the parent is a TYPE2 dimension table with both EFF FROM and EFF TO dates and the child table is a transaction table with a transaction date that can be used to find the AS WAS image of the dimension.
3. FIRST VERSION or LAST VERSION from parent as a join filter
Again the join key may not be uniquely identify a record from the parent table but you would like to join to the latest version of the matched record assuming that there is a column to identify the sort criteria.
This is a special case of #1
4. Use > or < criteria to find a nearest record
Again the join key may not be uniquely identify a record from the parent table. Here the parent table may have a EEF FROM date to indicate when the record becomes effective and the child table has a transaction date. We can specify the PARENT.EFF_FROM < CHILD.TRX_DATE to find the record with the greatest (latest) EFF_FROM date as of when the transaction occurred.
In addition, joins can be applied on a formula column that does not exist in the source table if the formula column itself is not depending on other parent table. This is for handling the case that we may need to cover the data type or get a portion, such as a substring from the source table column as the join key.