Reference an Alias Elsewhere in the Select List

Reference an alias elsewhere in the SELECT list

You can't refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:

SELECT 
  FirstName, LastName, Other, 
  Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
FROM 
(
  SELECT FirstName, LastName,
    CASE WHEN LastName = 'Jones'
     THEN 'N/A'
    END AS Other
  FROM dbo.table_name
) AS x;

Reference alias (calculated in SELECT) in WHERE clause

You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:

SELECT BalanceDue FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices
) AS x
WHERE BalanceDue > 0;

Or just repeat the expression:

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE  (InvoiceTotal - PaymentTotal - CreditTotal)  > 0;

I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.

PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.

Here are 5 example queries that all yield the exact same execution plan:

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;

SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;

Resulting plan for all five queries:

Reference an alias for an expression in a SELECT statement

You can't. But SQL Server has a very nifty feature where you can define the column in the from clause using apply:

SELECT v.newdate,
       Upper(LEFT(Rtrim(t1.[NameColumn4]),19) + v.newdate + RIGHT(t1.[NameColumn5], 3)) AS newname
FROM table1 t1 CROSS APPLY
     (VALUES ( <complicated expression here) 
     ) v(newdate)

Referring to a Column Alias in a WHERE Clause

SELECT
   logcount, logUserID, maxlogtm,
   DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)

Normally you can't refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.)

But, as mentioned in other answers, you can force SQL to treat SELECT to be handled before the WHERE clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):

Parenthesis/Subselect:

SELECT
   *
FROM
(
   SELECT
      logcount, logUserID, maxlogtm,
      DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
   FROM statslogsummary   
) as innerTable
WHERE daysdiff > 120

Or see Adam's answer for a CTE version of the same.

SQL Server : multiply column with alias name in select list

if you're writing a dynamic query, which it looks like you are, you can just put the variable in your equation

 declare @sql nvarchar(max)
 set @sql = 'select 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        ,' + @selectedColumn + ' as SelectedColumnSales ' + 
        ', t.CurrentPrice
         , (t.CurrentPrice * ' + @selectedColumn + ') as TotalRevenuePerItem

    FROM 
        dbo.SearchedUserItems t'
exec(@sql)

If I use an alias in a SELECT clause, how do I refer back to that alias?

SELECT Foo, Foo - col_c as Bar
from (
SELECT round(100*(col_a - col_b)/col_a, 1) as Foo,  col_c
FROM my_table
WHERE...

) t;

Select column by alias in MySQL

Short answer:

1. references to aliases in SELECT list or
2. Aliased expressions

The only documentation I've found on this so far has been:
https://bugs.mysql.com/bug.php?id=79549

In that link there is the following:

[9 Dec 2015 15:35] Roy Lyseng
...
Here is a longer background for the original decision:

Contrary to references to aliases in subqueries in the WHERE clause (and in GROUP BY, for that matter), there is no reason (except standard compliance) that we should not allow references to aliases in the SELECT list, since they should be available in the same phase of query execution. But the support in 5.6 was quite arbitrary:

Given this: create table t1(a int, b int),

Alias in SELECT list is not valid:

  select a+b as c,c+1 from t1;

ERROR 1054 (42S22): Unknown column 'c' in 'field list'

But within a subquery, reference to c is valid:

  select a+b as c,(select c+1) from t1;

And subquery must be after definition of alias:

  select (select c+1),a+b as c from t1;

ERROR 1247 (42S22): Reference 'c' not supported (forward reference in item list)

So, it is easy to say that support for references to aliases in SELECT list was rather ad-hoc. Nevertheless, we will try to reimplement the old solution, but with no attempt at cleaning up the obvious holes in the support for this feature. But referencing aliases in subqueries in the WHERE clause will not be reimplemented.

I'm yet looking for documentation beyond the bug report describing this functionality in the standard documents; but thus far no luck.

Can I use aliases within a select statement?

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

---

Related Topics