Bert Wagner looks at join order in SQL Server:
SQL is a declarative language: you write code that specifies *what* data to get, not *how* to get it.
Basically, the SQL Server query optimizer takes your SQL query and decides on its own how it thinks it should get the data.
It does this by using precalculated statistics on your table sizes and data contents in order to be able to pick a “good enough” plan quickly.
I like this post. It also lets me push one of my favorite old-time performance tuning books, SQL Tuning by Dan Tow. 95+ percent of the time, you don’t need to think about join order. But when you do, you want to have a systematic method of figuring the ideal join order out.