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

Answer 1

In the case of an INNER JOIN or a table on the left in a LEFT JOIN, in many cases, the optimizer will find that it is better to perform any filtering first (highest selectivity) before actually performing whatever type of physical join - so there are obviously physical order of operations which are better.

To some extent you can sometimes control this (or interfere with this) with your SQL, for instance, with aggregates in subqueries.

The logical order of processing the constraints in the query can only be transformed according to known invariant transformations.

So:

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id
WHERE a.something = something
    AND b.something = something

is still logically equivalent to:

SELECT *
FROM a
INNER JOIN b
    ON a.id = b.id
    AND a.something = something
    AND b.something = something

and they will generally have the same execution plan.

On the other hand:

SELECT *
FROM a
LEFT JOIN b
    ON a.id = b.id
WHERE a.something = something
    AND b.something = something

is NOT equivalent to:

SELECT *
FROM a
LEFT JOIN b
    ON a.id = b.id
    AND a.something = something
    AND b.something = something

and so the optimizer isn't going to transform them into the same execution plan.

The optimizer is very smart and is able to move things around pretty successfully, including collapsing views and inline table-valued functions as well as even pushing things down through certain kinds of aggregates fairly successfully.

Typically, when you write SQL, it needs to be understandable, maintainable and correct. As far as efficiency in execution, if the optimizer is having difficulty turning the declarative SQL into an execution plan with acceptable performance, the code can sometimes be simplified or appropriate indexes or hints added or broken down into steps which should perform more quickly - all in successive orders of invasiveness.