Concepts → Join
A join defines how one or more entity objects relate. A join can be a self join that creates hierarchical data, a join between two entity objects in the same physical schema, or a join between two entity objects in different physical schemas, known as a cross-schema join.
You can use the Join Editor to create a join. A join consists of at least one join condition, and in certain cases, an additional join filter.
The Join Editor supports only the creation of a Left-Outer join. The entity object on the left side of the join operator is the Child table. The entity object on the right side of the join operator is the Parent table. A query for a Left-Outer Join always returns all the rows from the Child table and only the rows from the Parent table where there is a match based on the join condition. A
Null value indicates a non-matched Parent table row for a Parent table column.
Using the Join Editor, you can create a join between any of the following physical schema entity objects:
During the Post-Load phase of a load job for a physical schema or a table, the Loader Service calculates the join and saves the results as a Direct Data Mapping file in Shared Storage. After saving a new join or a modified join in the Join Editor, the Loader Services also calculates the join and saves the results as a Direct Data Mapping file ins Shared Storage.
When using the Schema Wizard to create a physical schema, you have the option to allow Incorta to create joins automatically based on the Foreign key relationships between tables. Using the Table Editor or Join Editor, you can remove or edit these joins. You can also create new joins with the Join Editor.
You use the Join Editor to create new joins and edit existing ones. You use the Schema Designer or the Table Editor to remove an existing join. In order to edit a physical schema, you must belong to a group with the Schema Manager or the SuperRole role.. You also must either own the physical schema or have Edit access rights to the physical schema where the he Child entity object exists. To create a cross-schema join, you must also either own or have View access rights to the physical schema where the Parent entity object exists.
A cross-schema join between two entity objects shows in the Schema Designer for both physical schemas; however, you can manage these joins only by using the Join Editor or the Schema Designer for the physical schema where the Child object in the join exists.
You can use the Schema Diagram Viewer to view the joins for entity objects in a physical schema and the join conditions for each join relationship. If viewing the joins for a Child table in the Schema Diagram viewer, you can open the Join Editor to view and edit the join.
A join consists of one or more join conditions and zero or more join filters. At least one condition must be an Equijoin. An Equijoin uses the equal operator (
=). Typically, the join is between a Foreign Key in the Child table and a Primary Key in the Parent table.
A join is synonymous with a Left-Outer Join in the
ON clause of a SQL statement. The join condition describes the relationship between two entity objects. The Child table is on the left side of the join operator and the Parent table is on the right side of the operator.
A join condition and a join filter exist within the
ON clause of an SQL statement, combined by an
sch_HR physical schema with a join between the
tbl_departments tables. The following is an example of the SQL statement equivalent to a join with one join condition and a join filter.
|Query Result||A list with all employees from the Child table (
For more information about join conditions, see Concepts → Join Condition.
For more information about join filter, see Concepts → Join Filter.
Cardinality often characterizes the join relationship between two entity objects and describes the number of related rows. For example, you can represent the cardinality between tables as One-to-One, One-to-Many, and Many-to-Many relationships. Typically, a Foreign Key column and a Primary key column define the cardinality between the tables. Here are the available types of relationship that are possible:
- Self join
- One-to-One join
- Many-to One join
- Many-to-Many join
A cyclical join between two entity objects results in an error. The Join Editor will show an error message when you add a condition that results in a cyclical join. To avoid cyclical joins between two entity objects, create an alias and join to the alias.
In a self join, an entity object joins to itself. A self join results in a data hierarchy. The Loader Service generates two new columns for a self-joined table:
- Level (
_LVL): a numeral value starting with 0 that represents the level of the row in the hierarchy
- Is Leaf (
_LEAF): a boolean value,
false, that shows if the row is the last node in its path (true) or not the last node (false). The last node in a path does not have child nodes or branches.
Starting with the 5.1.2 release, Incorta Analyzer tables and Incorta SQL tables do not support self joins. When you try to load a table of these with a self join, it will throw errors.
An example of a Self join:
||For each row in the
In the Data panel of the Analyzer, you can select both the Level and Is Leaf columns
In a One-to-One join relationship, each row in the Child entity object is associated with one and only one row in the Parent entity object. Typically, the One-to-One join is between a Child and a Parent entity object using the Foreign Key and Primary Key, respectively, and the Foreign key has unique values. Examples of One-to-One joins may include the join between the
Driving Licenses tables and the
Social Insurance Records tables.
An example of a One-to-One join:
||For each row in the
In a Many-to-One join relationship, each row in the Parent entity object (the One side) is associated with one or more rows in the Child entity object (the Many side). Typically, the Many-to-One join is between a Child and a Parent entity object using the Foreign Key and Primary Key, respectively, and the Foreign Key has common values rather than unique values. Examples of Many-to-One joins may include the join between the
Order Details and
Orders tables, and
Departments tables. A Many-to-One relationship can also be between an entity object and its alias.
Examples of a Many-to-One join:
||A customer can have multiple orders. For each row in the
||Multiple employees can belong to one department. For each row in the
||Multiple employees can share the same manager. For each row in the
The Join Editor does not support the creation of a Many-to-Many relationship between two entity objects.
A Many-to-Many join relationship is a join between two entity objects where a row in one entity object is associated with multiple rows in the other entity object, and vice versa. Incorta does not support the implementation of a direct Many-to-Many relationship between two entity objects. However, you can break the Many-to-Many relationship into two Many-to-One relationships by using an intermediate or bridge table. The intermediate or bridge table references the Primary Keys in the original entity objects either as a composite Key or as Foreign Keys. The intermediate table represents the Many side in the two join relationships, that is, the Child object.
Examples of Many-to-Many joins may include the join between the Products and Customers tables where the Sales can function as the intermediate or bridge table.
You can specify a Full-Outer Join query for a materialized view and for an Incorta SQL Table.
An example of the Many-to-One join relationships that construct a Many-to-Many join relationship:
||A customer can buy multiple products, and multiple customers can buy one product. When creating a query that references columns from the
It is not recommended to create multiple join relationships between two entity objects. Instead, create one join relationship with multiple join conditions. Join conditions can be Equijoin conditions that use the equal operator (=) or range join conditions that use any comparison operator other than the equal operator; such as greater than (>), less than or equal to (≤), and not equal (≠). At least one condition must be Equijoin.
A join relationship that contains a range condition, in addition to the Equijoin condition, may result in multiple matches from the Parent object per a single row in the Child. However, as a Left-Outer join, for each row in the Child object, only one row will return from the Parent object. The range join operator influences the selection of Parent object rows. .
For example, if the Parent object has multiple rows that match a row in the Child entity object, and you use the greater than operator for the range join condition, only the row with the greatest value for the range join condition column is returned from all the matching rows that exist in the Parent object. While using the less than operator will return the row with the lowest or smallest value for the range join condition column.
To return all matching rows from the Parent entity object may require a Many-to-Many join relationship. To perform this, consider using a bridge table to create two Many-to-One join relationships.
Certain changes to either a join object or to the join itself may require a load job as follows:
- A Key column modification in an entity object that is part of a join condition requires a full load of the physical schema..
- For a Self join, a change in the join condition or join filter requires a table load.
Creating or updating the join conditions or filters does not require a full load as the Loader Service automatically calculates the join. However, in the case of a Self join, you need to perform a full load for the entity object.
A physical schema has a definable load order. For a Parent entity object that is not transaction based, consider adding the Parent to a group that loads before the group that has the Child entity object.