Query performance improves any time a join index can be used instead of the base tables. A join index is most useful when its columns can satisfy, or cover, most or all of the requirements in a query.
For example, the optimizer may consider using a covering index instead of performing a merge join.
When we are able to cover all the queried columns that can be satisfied by a join index, then it is called a cover query.
Covering indexes improve the speed of join queries. The extent of improvement can be dramatic, especially for queries involving complex, large-table, and multiple-table joins. The extent of such improvement depends on how often an index is appropriate to a query.
There are a few more join indexes that can be ...
