There was once a simple rule for joins—Index all the columns. As DBMSs have grown more sophisticated, and different join plans have become part of every DBMS's repertoire, the ways to optimize have become more varied and more subtle. Now, to get the most out of your DBMS, you need to know what the special situations are, and what the DBMS is getting up to.
The first thing to keep in mind is, when your work requires you to join information from multiple tables, you don't want to do the joins yourself. What you do want is to provide the optimum conditions so that your DBMS will perform the best join on its own—because it makes a big difference.
For example, if two tables, called
Table2, both have 1,000 rows and this SQL ...