Why I cannot use alias sometimes in SQL-CodePudding

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;

CodePudding user response：

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