There are various factors which can be considered for tuning Join conditions, below are few of them:
- We should use “=” (equality) as first after where condition.
- We should only use the range operators (like “Between” )where equality is not applicable.
- We should avoid negatives like != or NOT as much as possible.
- If possible then avoid matching patens i.e. LIKE.
- we should try to retrieve less or specific number of rows based on the conditions.
- Try to filter the large table at first to reduce row joined to it.
- It is always preferred to use most filer on the largest table because it reduces the row joins , retrieve the tables in order like most filtered used should be downwards.
- Index should be used on the tables to retrieve the records faster but preferred for very large tables except small tables.
Try to use optimizer and allow it do to its job.