What are the differences between these?

Question

What are the differences between the two queries?

   SELECT     CountryMaster.Id
   FROM       Districts INNER JOIN
   CountryMaster ON Districts.CountryId = CountryMaster.Id

   SELECT     CountryMaster.Id
   FROM       CountryMaster INNER JOIN
   Districts ON Districts.CountryId = CountryMaster.Id  

I know the output will be same, but I want to know is there any drastic effects of the same if I neglect positions of tables and columns in complex queries or tables having tons of data like hundreds of thousands of rows.

Answer 1

No difference whatsoever. The order of the joins is irrelevant. The query optimizer inside the database engine will decide on a merge plan to actually process the records from the two tables based on the stored statistics for the data in those tables.
In fact, in many cases, the query optimizer's will generate exactly the same plan for both a query phrased using joins as it would for a query phrased with a correlated sub-query.
The lesson here I have learned is:

Always start with the syntax, or representation, that most clearly represents the meaning of the process you are trying to create, and trust the query optimizer to do its job. Having said that, the query optimizer is not perfect, so if there is a performance issue, use the query show plan with alternate constructions and see if it improves...

One quick comment on performance of inner vs. outer joins. It is simply not true that inner joins are intrinsically faster than outer joins. The relative performance depends entirely on which of the three types of processing joins are used by the query engine;
1. Nested Loop Join, 2., Merge Join, or 3. Hash Join.
The Nested Loop join, for example, is used when the set of records on one side of the join is very much smaller than on the other side, and the larger set is indexed on the join column[s]. In this case, if the smaller set is the "outer" side, then an outer join will be faster. The reason is that the nested loop join takes the entire set of records from that smaller set, and iterates through each one, finding the records from the larger set that match. An inner join has to perform a second step of removing rows from the smaller side when no matches were found in the larger set. The outer join does not do this second step.

Each of the three possible types of join processes has its own characterisitic behavior patterns... See Nested Loop Joins, Merge Joins and Hash Joins for the details.

Answer 2

As written they are identical. Excellent answer from Charles.

If you want to know if they will have different execution plans then simply display the execution plan in SSMS.

As for speed have the columns used in the join indexed.
Maintain the indexes - a fragmented index is not nearly as effective.
The query plan will not always be the same.
The query optimizer keeps statistics and as the profile of the data changes the optimal plan may change.
Thousands of rows is not a lot.
Once you get into millions then tune indexes and syntax (with hints).
Some times you have to get into millions before you have enough data to tune.

There is also a UNION operator that is equivalent and sometimes faster.

The join hint Loop is not symmetric so in that case the query plan is different for the following but they are still that same results.

If one is a PK table I always put it first.
In this case the first is twice as fast as the second.

select top 10 docSVsys.sID, docMVtext.fieldID
from docSVsys 
inner loop join docMVtext 
   on docMVtext.sID = docSVsys.sID
where docSVsys.sID < 100
order by docSVsys.sID, docMVtext.fieldID

select top 10 docSVsys.sID, docMVtext.fieldID
from docMVtext
inner loop join docSVsys 
   on docMVtext.sID = docSVsys.sID
where docSVsys.sID < 100
order by docSVsys.sID, docMVtext.fieldID

Advanced Query Tuning Concepts