When you define a filter (a filter expression) in a join node, you have a possibility to optimize the runtime execution of the calculation view by mapping this filter between two (or more) sources of the join node. With filter mapping, when a filter is defined on a column from one source, you can ask the SQL optimizer to also apply this filter to a column of another other source.
Defining the filter mapping can be done graphically, in a similar way as defining joins. For this reason, the Join Definition tab offers two modes, to create either a Join or a Filter mapping when dragging a column from a source to a column of another source to create a connector.
Note
You can also choose to display Join connectors only, Filter mapping connectors only, or both.A Direction must also be specified, so that the optimizer knows from which side (left or right) an existing filter must be mapped to the other side. The default direction is Left ↔ Right, that is, bi-directional. A filter mapping connector has a dedicated color (orange) and a filter icon. One or two arrows show the mapping direction(s).
The improved performance results from the early execution of filters on both sources, before the join is executed. Therefore, filter mapping can be seen as a help to the optimizer in case the optimizer does not filter before executing the join - although it would make sense in your context. In the example shown in the figure, Filtering on Join, the optimizer does not know that product in the left table is the same thing as product_name in the right table. By mapping the filter on product to the column product_name, you can reduce the result set of Projection_2 to reduce the join execution time.
In some cases, filter mapping also helps ensuring that consistent data is returned.