sql server - WHERE clause better execute before IN and JOIN or after

Question

I read this article: Logical Processing Order of the SELECT statement

in end of article has been write ON and JOIN clause consider before WHERE.

Consider we have a master table that has 10 milion recored and a detail table (that has reference to master table(FK)) with 50 milion record.we have a query that whant just 100 record of detail table according a PK in master table.

In this situation ON and JOIN execute before WHERE?I mean that do we have 500 milion record after JOIN and then WHERE apply to it?or first WHERE apply and then JOIN and ON Consider?If second answer is true do it has incoherence with top article?

thanks

question from:https://stackoverflow.com/questions/5463101/where-clause-better-execute-before-in-and-join-or-after

Answer 1

It doesn't matter

Logical processing order is always honoured: regardless of actual processing order

INNER JOINs and WHERE conditions are effectively associative and commutative (hence the ANSI-89 "join in the where" syntax) so actual order doesn't matter

Logical order becomes important with outer joins and more complex queries: applying WHERE on an OUTER table changes the logic completely.

Again, it doesn't matter how the optimiser does it internally so long as the query semantics are maintained by following logical processing order.

And the key word here is "optimiser": it does exactly what it says