It seems to me that a lot of people is not getting really HOW a query is executed.

But that should be querying 101.

I would just point out the T-SQL Querying book by Itzik Ben-Gan, but let me recap.

A T-SQL query is composed by clauses, those are:

SELECT TOP(x) ....

FROM ...

WHERE ...

GROUP BY ....

ORDER BY ...

But this is a description of the query, not the real execution order.

If we where writing queries with clauses in the exact execution order, it would be in this way:

FROM ...

WHERE ...

GROUP BY ...

SELECT ...

ORDER BY ...

TOP ...

This is why you can write "Order by 1" and have the data sorted by the first column: because when the order by gets executed, the selected data is already there. However, you cannot use the expressions in the SELECT clause in the WHERE, FROM or GROUP BY clauses, because when those are executed, the select has still to be materialized.

What seems to trouble a lot of people is failing to grasp that the ON clause and the WHERE clause are executed in different moments. And, in between them, the OUTER kicks in.

So, the ON is used to match data for the join, and AFTER the join is evaluated, 1 of 4 things happens:

- if the join is INNER, nothing happens and no data is appended. This is why an INNER join will be always at least as fast as an outer

- if the join is LEFT, RIGHT or FULL OUTER, the non-joining data from the related table(s) will be added to the result set.

After this result set given by the FROM clause is built, the WHERE kicks in. No magic or difficult considerations.

Just always, ALWAYS remember the order of execution.

Everything else in the article is, IMHO, unnecessary clutter.