As a SQL Server practitioner, it is important to understand logical query processing. The following list is found in the book called T-SQL Querying by Itzik Ben-Gan et al, Microsoft Press, copyright 2015, in Chapter 1. The list below contains the general form of a query along with the step numbers assigned according to the order in which different clauses are logically processed. When you study SQL you are best to start with the SELECT statement. However, soon after you have tried several queries you must understand logical query processing if you are to advance in your studies. T-SQL Querying is an intermediate book on T-SQL and the first thing he discusses in chapter 1 (the most important chapter in Itzik’s opinion) is logical query processing.

The first sentence of the first chapter says: “Observing true experts in different fields, you will find a common practice that they all share—mastering the basics.”

Below is the logical query outline that is covered in the book.

• (5) SELECT (5-2) DISTINCT (7) <TOP_specification> (5-1) <select_list>
• (1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <join_predicate>
•       | (1-A) <left_table> <apply_type> APPLY <right_input_table> AS <alias>
•       | (1-P) <left_table> PIVOT (<pivot_specification>) AS <alias>
•       | (1-U) <left_table> UNPIVOT (<unpivot_specification>) AS <alias>
• (2) WHERE <where_predicate>
• (3) GROUP BY <group_by_specification>
• (4) HAVING <having_predicate>
• (6) ORDER BY <order_by_list>
• (7) OFFSET <offset_specification> ROWS FETCH NEXT <fetch_specification> ROWS ONLY

In most programming languages the code is processed in the order in which it is written. This is called typed order. In SQL the first clause that is processed is the FROM clause. The SELECT clause, which is typed first, is processed almost last. This is referred to the local processing order to distinguish it from the typed order and the physical processing order.

Each step in the logical processing order produces a virtual table that is used as the input of the next step. Only the table generated by the last step is available to the caller. If a certain clause is not specified in a query the corresponding step is simply skipped.

Virtual Tables

Under the hood, a query is processed in steps or phases. After each phase a virtual table is generated. We’ll call them VT1, VT2 and so on.

• FROM -> VT1
• WHERE -> VT2
• GROUP BY -> VT3
• HAVING -> VT4
• SELECT -> VT5
• evaluate expressions -> VT5-1
• distinct -> VT5-2
• ORDER BY -> virtual cursor VC6. Abscent an ORDER BY clause VT5-2 becomes VT6
• TOP | OFFSET-FETCH -> VC7 or VT7 from VC6 or VT6 respectively

First, get the tables and do the joins, if any. The very next thing is to filter with the WHERE clause. This filters our on a row-by-row basis. Each row of the resultant table from the previous step is examined to see if it passes the filter test in the WHERE clause (assuming there is a WHERE clause).