The general rule is that OUTER JOINs increase the number of rows in the result set, while INNER JOINs decrease the number of rows in the result set. Of course, there are many scenarios where the opposite is also true, but it rather works as it does not. What do you want to do for performance, since the size of the result set (working set) should be as small as possible.
Since both compounds coincide in the first table, changing the order does not affect the accuracy of the results. Therefore, you probably want to do an INNER JOIN before a LEFT JOIN:
SELECT * FROM TblA INNER JOIN DifferentDbCatalog.dbo.TblC on TblA.ID = TblC.TblAID LEFT JOIN freetexttable ( TblB, *, 'query' ) on TblA.ID = [Key]
As a practical matter, the query optimizer should be smart enough to compile to use the faster option, no matter what order you specify for the connections. However, itβs good practice to pretend that you have a dumb query optimizer and that the query operations are in order. This helps future maintainers identify potential errors or assumptions about the nature of the tables.
Since the optimizer needs to rewrite things, this is probably not good enough to fully explain the behavior you see, so you still want to study the execution plan used for each query, and possibly add an index as suggested earlier. This is still a good principle to learn.
source share