Joins with Non-Join Predicates

Joins will take advantage of additional non-join predicates when compound indexes exist that involve both the join condition and the predicate.

For example, a compound index exists on TABLE2 (A,B,C):

SELECT * FROM table1,table2 
    WHERE table1.a = table2.a 
      AND table2.b = 3 
      AND table2.c BETWEEN 1/1/1992 AND 12/31/1992;

If TABLE2 is an inner table of the join, the additional constraints will be applied when the join condition is applied.