The Logical Query Process
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
The filtering predicate can evaluate to true, false, or unknown.
The WHERE clause is evaluated before the SELECT clause, and therefore cannot refer to a column alias defined in the SELECT clause.
The WHERE clause is evaluated before rows are group, and therefore is evaluated per row.
The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.
If you want to refer to an element that is not part of your GROUP BY list, it must be contained within an aggregate function like MAX. This is because multiple values are possible in the element within a single group, and the only way to guarantee that just one will be returned is to aggregate the values.
T-SQL is based on multiset theory more than set theory which means if there are duplicates in the result set, it is your responsibility to remove them with the DISTINCT clause.
The ORDER BY clause is the first and only clause that is allowed to refer to column aliases defined in the SELECT clause because the ORDER BY clause is the only one to be evaluated after the SELECT clause.
When referring to a table, you must refer to the schema.table. It is best practice to do this.
In the FROM clause, you can define an alias with the form