Indexed Nested-Loop Join :
For each tuple tr in the outer relation R, use the index to look up tuples in S that satisfy the join condition with tuple tr
some materials mentioned that "Indexed Nested-Loop Join" only applicable for equi-join or natural join and an index is available on the inner relation’s join attribute
SELECT *
FROM tableA as a
JOIN tableB as b
ON a.col1 > b.col1;
Suppose we have an index on b.col1.
why Indexed Nested-Loop Join is not applicable for this case?
You are quoting slides for Database Systems Concepts (c) Silberschatz, Korth and Sudarshan.
We want the DBMS to calculate a join. There are lots of special cases where it can do it various ways. These might involve whether there are indexes, selection conditions, etc.
The particular technique that that book calls by that name works in certain cases:
Indexed Nested-Loop Join
If an index is available on the inner loop's join attribute and join is an equi-join or natural join
The answer is, because your query does not meet the conditions. It is not an equi-join (ie ON or WHERE a.col1 = b.col1) or natural join (USING (col1) or NATURAL JOIN).
As to why not meeting those conditions means not using that technique, it would be because it doesn't work and/or some other technique is better. You gave the technique :
For each tuple tr in the outer relation r, use the index to look up tuples in s that satisfy the join condition with tuple tr
If it's an inequality, you can't "look up in" the index; you have search through the index. Not this method.