Because join indexes generated from inner joins do not preserve unmatched rows, consider using outer joins to define simple join indexes, noting the following restrictions.
- Inequality conditions are not supported under any circumstances for ON clauses in join index definitions.
- Outer joins are not supported under any circumstances for aggregate join indexes.
Join indexes defined on outer joins can satisfy queries with fewer join conditions than those used to generate the index. See Defining a Simple Join Index on a Binary Join Result and Using Outer Joins to Define Join Indexes for a demonstration of this property.
Also see Restrictions on Partial Covering by Join Indexes and related examples.