<,>,<=,>= what are these operators in join ? please anyone give a brief on type of joins in incorta and how to perform all joins

Incorta supports the following types of joins:

• One-to-one (1:1)

• Many-to-one (M:1)

• Many-to-many (M:M) joins using Materialized Views

Example: Simple Many-to-one (M:1) Join

Tables:

• Sales table

• Customer table

The Sales table has multiple sales records for one customer. The Customer table has only one record for every customer.

Create a 1:1 join Incorta by:

1. Select Sales as the child table (left side in the Join screen).

2. Select Customer as the parent table (right side in the Join screen).

The join definition for this example is: Sales.Customer_id = Customer.Customer_id.

The join column in the parent table must be a unique primary key.

All joins in Incorta are left outer joins by default.  This means records in the child table (for example, Sales) are included in a query even if they don’t have a corresponding record in the parent table (for example, if customer_id is null, or Sales.customer_id does not exist in the Customer table).  If the report groups records by a field from the parent table (for example, CustomerName), records without a corresponding record display under the NULL bucket (CustomerName = NULL).

To transform an outer join to an inner join, add a NOT NULL filter on the parent column in the Insight.  For example, Customer.CustomerKey is NOT NULL.

To transform the default left outer join into a full outer join:

1. Select the Customer dimension in Analyzer.

2. Select Show Empty Group to see customers with no Sales records. You must select Show Empty Group for every dimension on which you want to see a right outer join.

You can create a join between two tables in different schemas. The join displays under both schemas (child and parent schema), but you can only edit it from the child schema.

Example: Complex Many-to-one (M:1) Join

Tables:

• Employee table with a dimension of type 2. The key is Employee_id, start_effective_date, end_effective_date

• Payroll table with a foreign key pointing to the Employee table

The join definition for this example is:

• Payroll.Employee_id = Employee.Employee_id
• Payroll.Txn_date >= Employee.start_effective_date
• Payroll.Txn_date < Employee.end_effective_date

This type of join is called a range join. Incorta requires an equi-join condition (for example, Payroll.Employee_id = Employee.Employee_id) in every range join definition.

Based on the example Hannah gave, but Incorta can also support a more complex scenario.  If the parent table Employee only have the start_effective_date, and no end_effective_date column, the following join condition will also work but more expensive in join calculation during data loading time:

• Payroll.Employee_id = Employee.Employee_id
• Payroll.Txn_date >= Employee.start_effective_date

What Incorta will do is to find all records from Employee table with the Employee_id matched from the child table Payroll.Employee_id and sort the records from Employee table with the same Employee_id by start_effective_date and join to the record with the same or the Max value (latest) in the start_effective_date which is greater than (earlier) than the Txn_date.

This behavior is to avoid you to get a random record returned from Employee table with the same Employee_id. 

This solution simplify the model so you don't need to write a ETL program or a MV to generate an end_effective_date column.

FYI, I added the above content as a KB article here: https://community.incorta.com/t/x17l91/how-joins-work-in-incorta.