When different operators like =, <, <=, >, >= or ≠ are used in the query, there are different techniques that a DBMS uses to evaluate it. Most commonly 3 of below methods are used to evaluate operators in the query.
Iteration
This method checks each record in one table against records in another table in a sequence. It is similar to evaluating nested loop join.
Indexing
if the columns used in the join condition has index, then the index is used to evaluate the expression rather than iterating all the records.
Partitioning
This is the grouping of set of columns into one block like in hashing. Usually records are sorted and created groups out of it. For example, in EMP we have different employees belonging to different age group ranging from 22 to 55. Now we can sort the employees based on their age, and can group them into 4 groups – 22 – 30, 31 – 40, 41-50 and 51-55. Hence if we want to search any record using the operators, the DBMS searches to particular partition block first and then iterates through the records in that block.
If the table is small, iteration method would be efficient. For larger tables indexing or partitioning would be efficient.