A join index can to be used to cover a wide variety of queries if the rows required in these queries form a subset of the row set contained in the join index. For example, consider a join index defined with the following SELECT query, where x1, x2 is a foreign key-primary key pair:
CREATE JOIN INDEX loj_cover AS SELECT x1, x2 FROM t1, t2 WHERE x1=x2;
Any query of the following form can use this join index, where c represents any set of constant conditions:
SELECT x1, x2
FROM t1 LEFT OUTER JOIN t2 ON x1=x2 AND c;
This property can significantly increase the applicability of join indexes.
Both the join index and the query are normalized to inner joins when the original form is defined with an outer join and there is a foreign key-primary key relationship between the join column set (see Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables than a Query). The result is that a less restrictive coverage test can be applied to both the query and to the join index.