Once all of the search clauses for each table have been analyzed, the join clauses are analyzed and optimized.
Each table is analyzed in the order listed in the from clause. dbcc traceon(302) prints the operator and table and column names, as shown in this sample output of a join between titleauthor and titles, during the costing of the titleauthor table:
Selecting best index for the JOIN CLAUSE:
titleauthor.title_id = titles.title_id
The table currently undergoing analysis is always printed on the left in the join clause output. When the titles table is being analyzed, titles is printed first:
Selecting best index for the JOIN CLAUSE:
titles.title_id = titleauthor.title_id
If you expect an index for a join column to be used, and it is not, check for the JOIN CLAUSE output with the table as the leading table. If it is not included in the output, check for datatype mismatches on the join columns.
