Extended Query Coverage With Outer Joins in Index Definition
A coverage algorithm determines that there is partial coverage, and the Optimizer uses the join index to join with the base tables to project the non‑covered columns if the cost is lower than performing the query using the base tables alone.
The join index optimizations introduced by extending query coverage through defining extra foreign key‑primary key joins does not negate the advantage of defining outer joins in your join index definitions because the normalization of outer joins to inner joins used by that optimization is specific to a particular class of queries. Queries that do not meet those specific criteria continue to benefit from the unnormalized outer join definitions in the join index.
Join indexes defined with outer joins can cover a query submitted in inner join format directly. Once the Optimizer converts the outer join in the query to an inner join by taking advantage of the equivalency of outer and inner joins for foreign key‑primary key relationships (see “Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query” on page 577), the system can make a coverage test instead of identical matching. In other words, the predicates in the join index definition and in the query need not be identical.