Just to clear something up. You absolutely can use an alias inside a SQL query but only after the alias has been created. It's important to clear that up.

SQL was written to be as close to English as possible which is why the SELECT statement comes first but the SELECT statement isn't processed first. This throws people. The major (keeping this simple for brevity): logical query processing phases are:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

So although SELECT appears first in a query, it's actually processed after FROM, WHERE, GROUP BY, etc... This is why you can't use an alias in your joins, WHERE clause, etc.. because SQL Server has not processed your SELECT statement and therefore the alias has not been created. You can, however, use an alias in the ORDER BY clause because that is processed after SELECT.

Here's a full chart by Itzik Ben-Gan: Logical Query Processing