Elements of Select Statement and how they are logically processed in SQL

Elements of Select Statement and how they are logically processed in SQL

This is my 50th blog post so i thought to write something interesting so here I am writing how a select query logically processed along with its elements  and  what's the order of elements of select query when logically processed..

The Select Clause are Logically Processed in Below Order.

  1-FROM

 2-WHERE 

 3-GROUP BY

4-HAVING

5-SELECT
6-DISTINCT

7-ORDER BY
8-TOP

Even though select comes first in the query but it is logically processed almost last

Lets take a example

Select empid,max(salary) as Empsalary

from Employee

where Empid between 1 and 100

group by empid

having max(salary)>10000

order by Empsalary desc

How this query logically Processed

--First Processing

From TableName

--next

Where Condition

--next

Grouping By 

--next

Having condition

--next

Order by 

Unfortunately, we cannot write the query in correct logical order. We have to start with the SELECT clause.

If you will see the above logical processing and query, Did you noticed that i have used aliasing for Max salary and same alias name i have given in Order by condition but not in Having Condition.

The Reason behind this is Having Processed before Select Clause and Order by after select clause, so after getting value in Select query  you are using alias name for order by but having Processed before select so if you will use alias name in Having Clause it will thrown an error.

--To check error run this query

Select empid,max(salary) as Empsalary

from Employee

where Empid between 1 and 100

group by empid

having Empsalary>10000

order by Empsalary desc

Error.

Invalid Column Name Empsalary .

I found it basic but yet interesting and less known Fact for developers.