Concepts → Join
About a 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.
Join Context
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.
Join Syntax
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 AND operator.
Consider the sch_HR physical schema with a join between the tbl_employees and tbl_departments tables. The following is an example of the SQL statement equivalent to a join with one join condition and a join filter.
| Join Element | Details |
|---|---|
| Condition | sch_HR.tbl_employees.DepartmentID = sch_HR.tbl_departments.DepartmentD |
| Filter | sch_HR.tbl_departments.DepartmentName IN ("HR","Sales") |
| SQL Statement | SELECT sch_HR.tbl_employees.Name, sch_HR.tbl_departments.DepartmentName FROM sch_HR.tbl_employees LEFT OUTER JOIN sch_HR.tbl_departments ON sch_HR.tbl_departments.DepartmentName IN (“HR”,”Sales”) AND sch_HR.tbl_employees.DepartmentID = sch_HR.tbl_departments.DepartmentID |
| Query Result | A list with all employees from the Child table (tbl_employees). For each row in the Child table where the department is “HR” or “Sales”, the department name is retrieved from the Parent table (tbl_departments); otherwise, the DepartmentName column will be Null. |
For more information about join conditions, see Concepts → Join Condition.
For more information about join filter, see Concepts → Join Filter.
Join relationships
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.
Self join relationship
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,trueorfalse, 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.
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:
| Example | Description |
|---|---|
HR.Employees.ManagerID = HR.Employees.EmployeeID | For each row in the Employees table, the value for the Level and Is Leaf columns are shown. |
In the Data panel of the Analyzer, you can select both the Level and Is Leaf columns
One-to-One join relationship
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 Drivers and Driving Licenses tables and the Citizens and Social Insurance Records tables.
An example of a One-to-One join:
| Example | Description |
|---|---|
Vehicles.Drivers.DriverD = Vehicles.DrivingLicenses.DriverID | For each row in the Drivers table, retrieve only the one row from the DrivingLicenses table where the values of the DriverID column in both tables are equal (match on equality). |
Many-to-One join relationship
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 Orders and Customers tables, Order Details and Orders tables, and Employees 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:
| Example | Description |
|---|---|
Sales.Orders.CustomerID = Sales.Customers.CustomerID | A customer can have multiple orders. For each row in the Orders table, retrieve only rows from the Customers table where the CustomerID in the Orders table is equal to the CustomerID in the Customers table. Orders associated with customers that are not registered in the Customers table will show Null values for columns from the Customers table. |
HR.Employees.DepartmentID = HR.Departments.DepartmentID | Multiple employees can belong to one department. For each row in the Employees table, retrieve only rows from the Departments table where the DepartmentID in the Employees table is equal to the DepartmentID in the Departments table. |
HR.Employees.ManagerID = HR.EmployeesAlias.EmployeeID | Multiple employees can share the same manager. For each row in the Employees table, retrieve only rows from the EmployeesAlias table where ManagerID in the Employees table is equal to the EmployeeID in the EmployeesAlias table. |
Many-to-Many join relationship
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:
| Example | Description |
|---|---|
● Sales.Sales.CustomerID = Sales.Customers.CustomerID ● Sales.Sales.ProductID = Sales.Products.ProductID | A customer can buy multiple products, and multiple customers can buy one product. When creating a query that references columns from the Sales, Customers, and Products tables, all the rows in the Sales table are retrieved and the related customer and product details appear where the join columns in both join relationships are matching. |
Additional Considerations
Multiple joins between two entity object
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.
Equijoin and Range join conditions
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.
Changes that require a load
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.
Load order for join
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.