The order in which are the SQL clauses entered into the query are quite known. However, the order of logical query processing is different than in which we enter it into the script.
SQL clauses and their order in the script:
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
but SQL query is logically processed in different order:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
Take clause SELECT as an example. We can see it is processed as penultimate even though it must be on the first place in the script. That means that all the preceding clauses FROM (1), WHERE (2), GROUP BY (3), HAVING (4) do not see what happens in SELECT (5) clause. Only clause ORDER BY (6) sees that. What impact does it have is shown in the example.
Logical SQL Query Processing Example
Let us take a look at the script which is wrong on purpose:
SELECT
[CustomerKey] AS Customer_ID
,[FirstName] AS First_NAme
,[LastName] AS Last_Name
,[BirthDate] AS Alias_Birth_Date
FROM [AdventureWorksDW2014].[dbo].[DimCustomer]
WHERE Alias_Birth_Date > '1940-01-01'
This query will result in an error because we defined alias “Alias_Birth_Date” in SELECT (5) and then we use this alias in clause WHERE (2). WHERE clause was already processed by the time the alias was created in the SELECT clause (so it doesn’t know it).
The situation will be different if we apply alias in ORDER BY. SELECT (5) is processed earlier than ORDER BY (6) therefore alias will be known and the query will be successful.
