Sql Logical Query processing phases

Hi all,

Here i am starting SQL Tour for us where you and me both will learn all required fundamental regarding SQL
so come with me for this beautiful tour

I am starting this tour with not typical t-sql or sql basics
i want to share with you how sql works ,how your query execute logically

So Lets start with Logical query processing phase

lets take a example

(8) select (9) distinct (11) ( top_specification .. select_list)
(1) from (left_table)
(3) (join_type) join (right_table)
(2) on (join_condition)
(4) where (where_condition)
(5) group by (group_by_list)
(6) with {cube | rollup}
(7) having (having_condition)
(10) order by(order_by_list)

don't be panic , i will explain you all this. you must be thinking why i put numbers?

First aspect of SQL that is different from other programming language is the order in which code is processed , In most programming languages code is processed in order in which it is written.

So lets start with How Sql process particular query

Each step generates a virtual table that is used as a input to the following step. this virtual tables
are not seen to caller, only table generated by the final step returned to the caller

in above example code is executed like this

1 . From : if join is present there then (cross join) join is performed between the two tables in the from clause and as a result virtual table VT1 is generated ..
ex: (from mstcustomer MC join mstdepartment MD)

2. On : the on filter is applied to VT1. only rows from which the is true are inserted to VT2 (virtual table 2)

3.Outer(join) : if an outer join is specified rows from the preserved table or tables for which match was not found are added to the rows from VT2 as outer rows, generating VT3

4.Where: where filter is apllied to VT3 . only rows from which(where_condition) is true are inserted to VT4

5.Group By : the rows from VT4 are arranged in a group based on the column list specified in group by clause . VT5 is generated

6.Cube | Roll up :supergroups are added to the rows from VT5, generating VT6

7.Having :having clause is applied to VT6 . only groups for which the is true is inserted to VT7

8.Select : the select list is processed , generating VT8

9.Distinct: duplicate rows are removed from VT8. VT9 is generated.

10.order by : the rows from VT9 are sorted according to the column list specified in the order by clause. A cursor is generated VT10

11.TOP: the specified numbers or percentage of rows is selected from the beginning of VC10. table VT11 is generated & return to caller ( that is your output)