When you hit execute, the first thing SQL Server does is make a plan of action for how to do what you said. In Management Studio, you can view estimated plans by pressing Ctrl + L. If you want to see the actual statistics and plan after execution, you can show actual plans with Ctrl + M. Microsoft has decided the best™ way of making a plan is to run a few estimates for what would work, and then pick the cheapest. If your query is complex enough™ then SQL Server will instead try just one way and then go with it because finding a new one is too hard. This is one reason, of many, that you should try to write T-SQL that is easy for SQL Server to understand. If you don’t you can get bad plans, that do things in unexpected ways. The only way to tell though is pop open the plan!
