can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?

Question

The documentation says that logical processing is used to know what I have available for the next clauses of a query, but later it contradicts itself by saying that the order can change, that confuses me.

(https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver16#logical-processing-order-of-the-select-statement)[here]

Logical Processing Order of the SELECT statement

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Warning

The preceding sequence is usually true. However, there are uncommon cases where the sequence may differ.

For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. In this situation, the CONVERT may execute before the WHERE clause executes. Uncommon indeed. Often there is a way to modify your view to avoid the different sequence, if it matters in your case.

1. Does this mean that, for example, there will be times when the WHERE clause is executed before the FROM clause and times when the FROM clause is executed first and then the WHERE clause?
2. Can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses?

I also read this from a StackOverflow expert on SQL:

SQL, by the way, is a descriptive language not a procedural language. A query does not specify the "order of execution". That is determined by the compiler and optimizer. What you are referring to is the "order of parsing", which explains how identifiers are resolved in the query.

3. So, what is the point of logical processing if physical processing (the database, optimizer, indexes, etc at the end of the day) decides the order of execution in a query?
4. Does this mean that physical processing can "skip" (alter, modify, I don't know what the right word is) the logical processing steps?
5. Does the logical processing is executed first and then the physical processing?, or how?

Answer 1

Logical processing is something you understand so you as a human can understand what you are asking the database to do when you write a SQL statement. There is functionally no code in the database that implements any sort of logical processing (I suppose you could argue that the part of the optimizer that parses the SQL statement and handles scope resolution implements some bits of logical processing if you squint at it just right).

The database optimizer implements physical processing of the statement. And physical processing can (and does) happen in whatever order the optimizer decides is likely to be most efficient. That may be to evaluate part of the where clause first, then evaluate one of the join conditions, then evaluate more of the where clause, then evaluate a function in the select list, then filter out more results with another piece of the where clause. There is no requirement (indeed it would be rather unusual) that one part of the statement gets fully evaluated at one point-- it is quite common that the actual query plan shows that steps are interleaved. And the optimizer is constantly getting updated information in the form of new statistics so it may choose to physically process the same SQL statement in completely different ways from one execution to the next.

If you are trying to figure out what results you are asking the database to return, look at how the statement is logically processed. If you are trying to tune the performance of a query once you are sure that it is returning the result you want, look at the query plan to see how the optimizer is actually processing it.