Using Alias in Query and Using It

Using alias in query and using it

You are talking about giving an identifier to an expression in a query and then reusing that identifier in other parts of the query?

That is not possible in Microsoft SQL Server which nearly all of my SQL experience is limited to. But you can however do the following.

SELECT temp, temp / 5
FROM (
    SELECT (a/b) AS temp
    FROM xyz
) AS T1

Obviously that example isn't particularly useful, but if you were using the expression in several places it may be more useful. It can come in handy when the expressions are long and you want to group on them too because the GROUP BY clause requires you to re-state the expression.

In MSSQL you also have the option of creating computed columns which are specified in the table schema and not in the query.

Why can't I use column aliases in the next SELECT expression?

You can use a previously created alias in the GROUP BY or HAVING statement but not in a SELECT or WHERE statement. This is because the program processes all of the SELECT statement at the same time and doesn't know the alias' value yet.

The solution is to encapsulate the query in a subquery and then the alias is available outside.

SELECT stddev_time, max_time, avg_time, min_time, cnt, 
       ROUND(avg_time * cnt, 2) as slowdown
FROM (
        SELECT 
            COALESCE(ROUND(stddev_samp(time), 2), 0) as stddev_time, 
            MAX(time) as max_time, 
            ROUND(AVG(time), 2) as avg_time, 
            MIN(time) as min_time, 
            COUNT(path) as cnt, 
            path
        FROM 
            loadtime
        GROUP BY
            path
        ORDER BY
            avg_time DESC
        LIMIT 10
   ) X;

How do I use alias in where clause?

The SQL-Server docs says:

column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.

Similar in the MySQL doc it says:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

In MySQL you can at least reuse aliases in the SELECT clause

Using alias in the same select

No, you can't do this in the same SELECT statement, but you can use subquery:

SELECT 
  TotalFoo,
  TotalBar,
  TotalFoo + TotalBar AS TotalFooBar
FROM
(
    SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
           SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
           ...
    FROM ...
) AS sub
...

or a CTE:

WITH CTE
AS
(
   SELECT SUM(foo.A + foo.B + foo.C + foo.D + foo.E) AS [TotalFoo],
          SUM(bar.A + bar.B + bar.C + bar.D + bar.E) AS [TotalBar],
          ...
   FROM ...
)
SELECT 
  TotalFoo,
  TotalBar,
  TotalFoo + TotalBar AS TotalFooBar
FROM CTE

What are some rules to follow when using alias names along with subqueries?

Lets look at the first query.

SELECT ID
FROM MyTable AS t
WHERE Value = (SELECT MAX(Value) FROM t)

This would try to select from the table alias t.

Which isn't allowed. It should select from a table or view.

For example:

SELECT ID
FROM MyTable AS t
WHERE Value = (SELECT MAX(Value) FROM MyTable)

Note that this doesn't have to do with scope.
It's also not allowed without using any sub-queries.

For example, this would fail for the same reason

SELECT t1.*, t2.*
FROM MyTable AS t1
JOIN t1 AS t2 ON t2.id = t1.id

Then we have the second query.

SELECT ID, Value,
( 
    SELECT COUNT(Value) 
    FROM MyTable t1sub
    WHERE t1sub.Value >= t1out.Value
) AS Rank
FROM MyTable AS t1out

This is called a correlated subquery.

That sub-query links the current t1out.Value of the outer query to the table in the sub-query via t1sub.Value.

It re-executes the sub-query for each row from the outer query.

About the scope.

It's about what a part of the SQL can look at and use.

In the previous SQL, the t1sub alias is only known within the scope of the correlated sub-query.

The outer query doesn't even know about the alias t1sub that's used in the sub-query.

While inside the correlated subquery, the t1out.Value can be seen and used.

Some tests can be found on db<>fiddle here

Does using Aliases with AS slow down Query Speed?

Not at all and the performance impact is negligible. Alias give you much better time readability in the query as it removed ambiguity.

Use alias name in same query for SQL Server

You can use an alias in the same query, but... If you need to use an alias of an aggregate function (such as SUM(amount) AS aggregated_alias, AVG(), COUNT(), etc) in the same query, you must use a subquery.

Here is a sample subquery:

SELECT 
a.QUANTITY,
a.AMOUNT,
SUM(a.CntAmount) AS SUM_CNTAMOUNT
FROM (SELECT 
      QUANTITY, 
      AMOUNT, 
      Count(AMOUNT) AS CntAmount
      FROM   MY_TEST 
      GROUP  BY QUANTITY,  AMOUNT) AS a
GROUP BY a.QUANTITY, a.AMOUNT

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.

---

Related Topics