T-SQL Basics: SELECT statement logical processing

This will be the first post in the T-SQL Basics series, where i try to explain some basic concepts and usage of common T-SQL commands, and this time i decided to cover the logical processing of the select command and an error that I often see in some developers.

To start, a select statement looks something like this:

SELECT select_list [ INTO new_table ]
[ FROM table_source ] 
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ] 

With this in mind, let's run a simple query to obtain product with price greater than $100.00

SELECT ProductID, Name, ListPrice
FROM [Production].[Product]
where ListPrice > 100.00

You obtain the following output:

Let's assume price is in USD

With this in mind, let's suppose you want the price in GTQ, this should be a piece of cake, right?, using what we know, we could expect to run a query like this:

SELECT ProductID, Name, (ListPrice *7.8) as [PriceGTQ] 
FROM [Production].[Product]
where [PriceGTQ] > 100.00

RIGHT? no. Instead, you obtain an error like this:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'PriceGTQ'. 

Why is this? because of the logical processing of the SELECT statement: 
The FROM clause is evaluated first, after that, in our query, the WHERE is evaluated, based on the fields obtained in the previous clause, so at this time, the PriceGTQ column does not exists, and therefore the error is shown.

To fix this, a simple approach is to copy the field declaration in the WHERE clause:

SELECT ProductID, Name, (ListPrice *7.8) as [PriceGTQ] 
FROM [Production].[Product]
where (ListPrice *7.8) > 100.00 --PriceGTQ definition

This way, the query is executed without issues.

Because the ORDER BY is evaluated after the SELECT clause, you can use the PriceGTQ label without issues:

SELECT ProductID, Name, (ListPrice *7.8) as [PriceGTQ] 
FROM [Production].[Product]
where (ListPrice *7.8) > 100.00 --PriceGTQ definition
order by [PriceGTQ] 

You obtain the following output:

According to the Microsoft documentation, the logical query processing for all the clauses is this:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP 

As you can see for our example above, the logical processing order is:

FROM > WHERE > SELECT > ORDER BY

This could help you improve your code, avoiding you to repeat unnecessary code or troubleshoot non-existent columns quickly, Making it more readable and efficient.

Source:
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql