Using alias in query and using it

Question

I have a doubt and question regarding alias in sql. If i want to use the alias in same query can i use it. For eg: Consider Table name xyz with column a and b

select (a/b) as temp , temp/5 from xyz

Is this possible in some way ?

Answer 1

You can use Oracle with statement too. There are similar statements available in other DBs too. Here is the one we use for Oracle.

with t
as (select a/b as temp
from xyz)
select temp, temp/5
from t
/

This has a performance advantage, particularly if you have a complex queries involving several nested queries, because the WITH statement is evaluated only once and used in subsequent statements.

Answer 2

Not possible in the same SELECT clause, assuming your SQL product is compliant with entry level Standard SQL-92.

Expressions (and their correlation names) in the SELECT clause come into existence 'all at once'; there is no left-to-right evaluation that you seem to hope for.

As per @Josh Einstein's answer here, you can use a derived table as a workaround (hopefully using a mote meaningful name than 'temp' and providing one for the temp/5 expression -- have in mind the person who will inherit your code).

Note that code you posted would work on the MS Access Database Engine (and would assign a meaningless correlation name such as Expr1 to your second expression) but then again it is not a real SQL product.

Answer 3

Its possible I guess:

SELECT (A/B) as temp, (temp/5)
FROM xyz, 
(SELECT numerator_field as A, Denominator_field as B FROM xyz), 
(SELECT (numerator_field/denominator_field) as temp FROM xyz);

Answer 4

This is now available in Amazon Redshift

E.g.

select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data;

Ref:

https://aws.amazon.com/about-aws/whats-new/2018/08/amazon-redshift-announces-support-for-lateral-column-alias-reference/

Answer 5

You might find W3Schools "SQL Alias" to be of good help.

Here is an example from their tutorial:

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'

Regarding using the Alias further in the query, depending on the database you are using it might be possible.

Answer 6

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.