Semijoin with Non-Equality Conditions Scenarios
Semijoin optimization can also apply to joins that use non-equality conditions (non-equi-joins).
Ideally, full statistics and cardinalities have been gathered on the tables, or predicate cardinality has been specified explicitly by query option in the view SQL. The semijoin can be used when tables are joined on an equality or non-equality condition. Non-equality semijoin optimizations require that table cardinality be specified; otherwise, the query is forced to use a less than optimum merge strategy.
For example:
SELECT * from DS1.R LEFT OUTER JOIN DS2.T on r1 > t1 and r1 <= t2
The target side predicate always uses the OR form, and looks like this:
(1 > t1 and 1 <= t2) OR (6 > t1 and 6 <= t2) …)
The semijoin target is always the immediate child of the join.
In some cases the target is not the immediate child of the join. For example:
SELECT * from DS1.R INNER JOIN DS2.S INNER JOIN DS3.T on s1=t1 on r2=s2
The topology for this query example looks like this:
Two semijoins are possible. J2 can have S as source and T as target, and J1 can have R as source and S as target. Both joins can use the semijoin optimization at the same time. The definition of source and target of a semijoin needs to be refined.
For an inner join or a left outer join with semijoin, the immediate LHS child of the join is the source, and the target is a FETCH against a data source found in the RHS sub-tree of the join. The ON clauses that define table connections determine the specific FETCH node target. For right outer join, the definition is the reverse.
A join with a semijoin optimization can have more than one target. For example:
SELECT * from DS1.D1 INNER JOIN DS2.F INNER JOIN DS1.D2 on ds1=f1 on ds2=ds1
The join with D1 as source can target both F and D2. Because multiple nodes target DS1, this join can be evaluated as a star schema semijoin.
