Concepts → Join Filter
A join filter is a filter expression for a Parent entity object in a join. The filter expression functions to filter rows in the Parent entity object.
As such, a join filter often functions to define the uniqueness of Parent entity rows. This is especially helpful when the Parent entity does not have a Key column or the Key column is not specified in the join condition.
In this regard, a join filter is synonymous with the
ON clause in a SQL Join statement. Although both the join filter and join condition are synonymous with the
ON clause in a SQL statement, the join condition compares the values of the two columns in the join using the condition operator while the join filter compares the value of a column from the Parent entity object with a specified value (except for the First Version and Last Version operators as these behave differently).
A join filter does not affect a self-join, that is, a join where the same entity object is both the child and the parent.
The Loader Service evaluates the join filter expression and creates a direct data map when saving the join details or when loading the related physical schema(s) or entity object(s). The Analytics Service reads the join direct data map for queries that reference the join entities.
You use the Join Editor to manage filters for a given join. You have to be in a group with the Schema Manager or the SuperRole role to access the Schema Manager. You also 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.
A join that you create between entity objects in different physical schemas shows in the Schema Designer for both physical schemas; however, you can manage these joins only by using the Join Editor for the physical schema that has the Child entity object in the join.
A join filter expression consists of:
- a filter column
- a filter operator
- a filter value
For a join filter, you can reference only columns from the Parent entity object. The following are the types of columns that you can reference in a join filter.
- Physcial_Schema.Incorta_SQL_Table.Column (including computed columns)
To learn more, review Concepts → Join Filter Column.
It is not recommended to reference a formula column in an entity object in another schema for the join filter column.
The following are the available operators to select from for a join filter operator:
<: less than
≤: less than or equal to
=: equal to
<>: not equal to
>: greater than
≥: greater than or equal to
IN: a value from an array of scalar values
First Version: the lowest value in the join filter value column for each group in the filter column
Last Version: the greatest value in the join filter value column for each group in the filter column
When using the
First Version or the
Last Version as the join filter operator, You need to select columns from the Parent object for both the filter column and the join filter value. In this case, the following applies:
- The Loader Service groups data in the Parent entity object by the filter column and sorts data in each group by the join filter value column.
- In the case of the
First Version, the Loader Service retrieves only the row with the lowest (first) value in the join filter value column for each group that has a matching record in the Child entity object.
- In the case of the
Last Version, the Loader Service retrieves only the row with the greatest (last) value in the join filter value column for each group that has a matching record in the Child entity object.
For a join filter value, you can use one of the following depending upon the operator that you select:
- a scalar value for comparison operators (
- an array of scalar values for the
- a column in the Parent entity object only in the case of
In addition to numeric and date scalar values, you can use comparison operators with string or text values. In such a case, the join filter column is sorted in an alphabetical order, and then compared to the entered value. For example, when you enter the following filter expression: State < Colorado, all cities that precede Colorado in the alphabetically ordered list will be retrieved; that is, Alabama, Alaska, Arizona, Arkansas, and California.
A date needs to be in the same date format as the filterable column. In the Table Editor, you can preview the column details and view the sample data in order to discern the date format.
You can reference a date system variable for the filter value. However, the technique serves only to capture a snapshot of the system variable value.The Loader Service evaluates the value when saving changes to a join in the Join Editor, during the load job for the entity object, or during the load job for the related physical schema.
The following table shows the available join filter operators, the supported values, and the effect of this filter on the rows retrieved from the Parent entity object after applying the join condition(s).
|Age||<||50||All rows from the Parent table that have a matching for the join column(s) and where the Age column is less than 50 are retrieved.|
|Position||≤||SW Engineer||All rows from the Parent table that have a matching for the join column(s) and where the Position column is less than or equal to SW Engineer are retrieved.|
|Department||=||IT||All rows from the Parent table that have a matching for the join column(s) and where the Department column is equal to IT are retrieved.|
|Country||≠||USA||All rows from the Parent table that have a matching for the join column(s) and where the Country column is not equal to USA are retrieved.|
|State||>||California||All rows from the Parent table that have a matching for the join column(s) and where the State column is greater than California are retrieved.|
|Salary||≥||1000||All rows from the Parent table that have a matching for the join column(s) and where the Salary column is greater than or equal to 1000 are retrieved.|
|City||IN||Cairo,London,Boston||All rows from the Parent table that have a matching for the join column(s) and where the City column is Cairo, or London, or Boston are retrieved.|
|Position||First Version||Salary||From the Parent table, only the row with the lowest Salary per Position that has a matching record in the Child table is retrieved.|
|Department||Last Version||Hire_Date||From the Parent table, only the row with the latest (greatest) Hire_Date per Department that has a matching record in the Child table is retrieved.|
The following is an example of the SQL statement equivalent to a join with one condition and a filter. Consider the
sch_HR physical schema with the
tbl_departments tables. A join created with the following condition and filter details:
|Query Result||A list with all employees from the Child table (
Adding a join filter to a join is different from a SQL statement that has a filter expression in a
WHERE clause or an
In a physical schema, a join between Child and Parent entity objects is a Left Outer Join. In a SQL statement, the
WHERE clause defines a filter expression that removes rows after the join operation. Because a physical schema join is a Left Outer Join, it is not possible to push the predicate into the join itself as it would be possible with an Inner Join.