Why I cannot use alias sometimes in SQL

Question

Hi guys I know this is a basic question but please help me establish basic concepts

For the bold line- (((p2.size) - p1.size)/p1.size * 100.0) AS growth_perc why cant i use size2015/size2010 to replace p2.size/p1.size? Thank you guys

-- Code below

SELECT p1.country_code,
   p1.size AS size2010, 
   p2.size AS size2015,
   -- Calculate growth_perc
   **(((p2.size) - p1.size)/p1.size * 100.0) AS growth_perc**
-- From populations (alias as p1)
FROM populations AS p1
-- Join to itself (alias as p2)
INNER JOIN populations AS p2
-- Match on country code
ON p1.country_code = p2.country_code
    -- and year (with calculation)
    AND p1.year = p2.year - 5;

The short answer is that all elements in each clause are evaluated all at once, rather than element by element. See https://sqlbolt.com/lesson/select_queries_order_of_execution for a brief discussion on when various clauses get evaluated. — Stuart Ainsworth, Jul 19 '22 at 22:55
Some DBMSs do allow you to do this, but I guess mysql is not one of them — NickW, Jul 19 '22 at 23:00

score 1 · Accepted Answer · answered Jul 20 '22 at 00:59

You cannot reference a current column alias, as the alias is still a temporary column name.

One way that you can use the alias is to wrap it into a sub query like this:

select x1.country_code 
       x1.size2010,
       x1.size2015,
       -- Calculate growth_perc
       (((x1.size2015) - x1.size2010)/x1.size2010 * 100.0) AS growth_perc
from (
   SELECT p1.country_code,
          p1.size AS size2010, 
          p2.size AS size2015
   -- From populations (alias as p1)
   FROM populations AS p1
   -- Join to itself (alias as p2)
   INNER JOIN populations AS p2
   -- Match on country code
   ON p1.country_code = p2.country_code
   -- and year (with calculation)
   AND p1.year = p2.year - 5;
) as x1

Why I cannot use alias sometimes in SQL

1 Answers1