0

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.

  1. 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?
  2. Does this mean that physical processing can "skip" (alter, modify, I don't know what the right word is) the logical processing steps?
  3. Does the logical processing is executed first and then the physical processing?, or how?
3
  • The logical processing and physical processing are two quite unrelated things, which may be why you're confused. Will try to explain in an answer if I get a chance.
    – J.D.
    Nov 14, 2023 at 16:17
  • Hi @J.D. , I will wait your answer with more details :)
    – jwa
    Nov 14, 2023 at 17:36
  • Cross-post on Stack Overflow stackoverflow.com/questions/77481764/… Nov 15, 2023 at 12:46

1 Answer 1

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.

9
  • hi @Justin Cave - Can we say that the SELECT, FROM, etc clauses (logical processing) are the "interface" for the sql programmer and the physical processing are the "implementation details", the "how" these clauses are executed, something like encapsulation in object oriented programming?
    – jwa
    Nov 14, 2023 at 17:41
  • @jwa - Logical processing is the "what" and physical processing is the "how". You could draw some parallels with encapsulation in OOP but it isn't a precise analogy Nov 14, 2023 at 17:45
  • So, what exactly do you mean by the "what" and the "how"? I hear that a lot in this context @Justin Cave
    – jwa
    Nov 14, 2023 at 17:49
  • @jwa - Logical processing tells you what the query will return. It lets you understand what question you are asking the database to answer. Physical processing tells you how the database will return that result. That's what you look at if you want to know why the query you wrote is taking so long to return the result. Nov 14, 2023 at 17:52
  • Can I rely 100% on logical processing to know which objects (columns aliases, tables, etc) I have "available" in subsequent query clauses? @Justin Cave
    – jwa
    Nov 14, 2023 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.