Can I use aliases within a select statement?

Question

I would like to write a statement that looks like this

select 1 as one, 2 as two , one + two as three

However SQL Server cannot work out what one + two is.

I can re-write in this form

SELECT x.one
      ,x.two
      ,x.one + x.two AS three
FROM (
    SELECT 1 AS one
          ,2 AS two
    ) x

Which gives me the output I expect. It is just a bit messy (and more so in a non-contrived example) I am also not sure what this sort of thing does to the internals of SQL Server and what implications there are to speed of execution.

Is there a better way of doing this?

Answer 1

You cannot refer to an alias in the same SELECT, you need to define it in a sub-query(like you did) or in a Common-table-expression(CTE):

WITH CTE AS
(
    SELECT 1 as one, 2 as two
)
SELECT one, two, one + two AS three FROM CTE

Or with this syntax:

WITH CTE(one, two) AS
(
    SELECT 1, 2
)
SELECT one, two, one + two as three from CTE

The same rule applies to the WHERE: Reference alias (calculated in SELECT) in WHERE clause

But normally it doesn't hurt if you use the same expression multiple time, the sql server optimizer will evaluate it only once. So you could do:

SELECT 1 as one, 2 as two , 1 + 2 as three