Logical Query Processing In T-SQL

There are 2 types of query execution in T-SQL: logical query execution and physical query exectuion.

Logical Query processing is a declarative manner of defining query. It answers the question “what?” Meanwhile, physical execution is an imperative way of defining query, and answers the question “How?”.

So, what a client should retrieve is defined by Logical execution. Physical execution is something like an instance of logical execution and provides a way of retrieving data, defined by logical execution. Physical execution can change the order logical execution steps only if the result will remain the same defined by logical execution.

Most of the time, learning logical execution internals will help you to understand why you write it in this way.

Logical Query execution Phases

As you know, when writing T-SQL queries we have defined steps for statements. They are:

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY
7. OFFSET..FETCH

While the above ordering is correct for physical execution, Logical processing executes these statements in a slightly different order. Logical execution order is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. OFFSET..FETCH

Practical Values of Logical Execution
 

1. You don’t have an access to the aliases created in SELECT phase from FROM phase.

As you already know, we can define aliases if we want to rename the column and also calculated and concatenated columns. In this case, accessing these columns through WHERE is not possible. Because WHERE is executed before SELECT and it is not possible to execute the same statement twice in execution lifecycle, the ordering of statements is one way operation.

2. You can’t access the same alias in SELECT twice.

Even if you declare an alias in the SELECT clause, it is not possible to use the same alias twice. The reason is that every statement in logical execution is transactional/atomic. T-SQL sees every statement “in a single lined” operation. For that reason, you can’t use the same alias twice.

3. WHERE doesn’t work for Group By.

As I already mentioned, statements’ logical execution is a one-way operation. This means there is no possibility to change the order (only if physical execution not changes them). WHERE will be executed before Grouping operation. For that reason, there is no way to apply filter for grouped information (GROUP BY) using WHERE. Inevitably, the provider created another filtering statement (HAVING) for grouped data.

4. SELECT picks only those columns, which are defined by GROUP BY.

Every statement has its own input and output points. They receive some data as an input, process it, and output some data as a response. Every statement can only receive the data processed by a previous statement.

For the given reason, you can pick/select only those columns, which are processed by Group By.

5. ORDER BY can see aliases created by SELECT.

This is also understandable because ORDER BY will be executed only after SELECT. Every statement can see the data defined by previous statement.

Conclusion

It is imperative to understand the logical execution steps and their impact.

Understanding these things will help you to understand why some of the limitations on query writing exist.