Concepts → Join Condition Column
About a join condition column
A join condition column is a data-backed or formula column that defines the join between two physical schema entity objects or between a physical schema entity object and itself.
A join condition column references a column in the Child entity object (that is, the entity object in the left side of the join) or a column in the Parent entity object (that is, the entity object in the right side of the join). The join condition columns, along with the join operator, construct the join condition that defines the relationship between the entity objects.
You specify a join condition in a physical schema using the Join Editor. In the Join Editor, for the join condition columns in both the Child or Parent sides, you specify the fully qualified name of the column: the physical schema, entity object, and column.
A join condition must be unique for the entities. In other words, it is not possible for identical join conditions for the same join or other joins in the same physical schema.
You need to either own the physical schema of the Child object or have Edit access rights to it in order that you can access the Join Editor and manage the physical schema joins. In the case of a cross-schema join, you need to own both physical schemas or, at least, have Edit access rights to the physical schema of the Child object and View access rights to the physical schema of the Parent object.
About a join Child column
The column that you can select for the Child side must be from an entity object in the physical schema you edit, and it is typically a Foreign Key in the Child entity object referencing a Primary Key column in the Parent entity object. However, it can be any column with a supported data type. For all conditions, the columns in the Child side are from the same Child entity object in the first join condition.
About a join Parent column
The column in the Parent table is typically a Key column or a column with unique values. The Child column and the Parent column must be the same data type.
A Parent column that is not a unique row identifier such as a Key column will randomly return duplicated rows for the join condition. This often leads to inconsistent data. If a Parent column is not unique, consider using a Join Filter to help create uniqueness for rows in the Parent table.
The Parent column can be any of the following:
- a column from the same entity object as the Child column (self join)
- a column from an alias for the Child entity object
- a column from an entity object in the same physical schema as the Child entity object
- a column from an entity object in another physical schema (cross-schema join). You need to own the other physical schema or at least have View access rights to it.
Do not select the Child and Parent columns from the same Incorta Analyzer table or Incorta SQL table. Starting with the 5.1.2 release, these tables do not support self joins. When you try to load an Incorta Analyzer table or Incorta SQL table with a self join, it will throw errors.
A join between an entity object and itself creates a data hierarchy. The data hierarchy differs from a traditional self-join in a SQL statement. To create a self-join relationship that is similar to a self-join in a SQL statement, first create an alias and then a join between the physical schema entity object and the alias.
Supported data types for a join condition column
The following table shows the supported and recommended data types for columns that you can reference in a join condition for the Child or Parent side:
| Data type | Supported | Recommended |
|---|---|---|
| Boolean (in the case of using formula columns that return a boolean value) | ✓ | ✓ |
| Date | ✓ | ✓ |
| Double | ✗ | |
| Integer | ✓ | ✓ |
| Long | ✓ | ✓ |
| Null | ✗ | |
| String | ✓ | ✗ |
| Text | ✓ | ✗ |
| Timestamp | ✓ | ✗ |
A join condition allows for the join columns to have similar, but different data types such as an Integer and a Long. Implicit conversions between types may produce false or missed joins.
It is not recommended to use a text or string column for the join filter column as string comparisons can be slow for large data volumes.
Supported columns to reference for a join condition column
For each join condition column you specify the fully qualified name of the column by selecting the physical schema, entity object, and column.
The following are the columns that you can reference in a join condition::
- Physical_Schema.Table.Column
- Physical_Schema.Table.Formula_Column
- Physical_Schema.Incorta_Analyzer_Table.Column
- Physical_Schema.Incorta_Analyzer_Table.Formula_Column
- Physical_Schema.Materialized_View.Column
- Physical_Schema.Materialized_View.Formula_Column
- Physical_Schema.Alias.Column
- Physical_Schema.Alias.Formula_Column
- Physical_Schema.Incorta_SQL_Table.Column (including computed columns)
It is not recommended to reference a formula column in an entity object in another physical schema for the join condition column.