Calculations on the projection (select) values are useful and common. Yet column aliases declared in the projection can't be used there.

select sellprice as x, cost as y, x/y as markup  
from mytable

gives an error.

I can accomplish this with

select sellprice as x, cost as y, sellprice/cost as markup  
from mytable

But that is a simple example: We usually declare column aliases names with as for complex cases. For example, instead of cost, I might have some function built from average etc, that I declare as a column alias.

How can I use these aliases inside the projection clause?


The usual way to cope with that is to use a derived table (aka sub-query):

select x, y, x/y as markup
from (
  select sellprice as x, cost as y
  from mytable
) as t;
  • Thank you. Any insights on why SQL requires this workaround where select sellprice as x, cost as y, x/y as markup from mytable seems to make more sense? – Joshua Fox Jun 24 '19 at 8:48
  • 1
    @JoshuaFox: that's how the SQL syntax was defined. You would need to ask the SQL standard committee for why that decision was taken. My guess(!!) is that using "forward" references was simply deemed to be too complicated for parsers back in 70's – a_horse_with_no_name Jun 24 '19 at 8:50
  • 2
    @JoshuaFox, renaming ( as ) happens last. Note that the DBMS is free to change the order of evaluation, as long as the semantics are preserved. In a query (though stupid) like select x as y, y as x, x/y from t the order in which renaming happens matters. – Lennart Jun 24 '19 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.