Aliases in calculations for MySQL

Question

I am in the process to move a really small database from Access to MySQL and I have some queries to translate.

I have this really simple query (in Access db):

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage ;

This does not work in MySQL, because it does not allow column aliases in formulas. I learnt here that the solution is to wrap the alias with a (SELECT alias). That works, but the result is really ugly and becomes unreadable. In my case it is:

((SELECT ((SELECT CalcField1) - (SELECT CalcField2))) / (SELECT CalcField2)) AS Percentage

In the former case I can understand immediately the expression, in the latter I cannot.

My question is: is there an alternative way to use aliases in MySQL formulas that have a better readability?

Answer 1

It's more readable if you use a derived table. No need for 4 SELECT clauses, just 2. Plus, it's proper SQL, not some fancy feature that MySQL or Access has added:

SELECT InvoiceNumber,
       CalcField1,
       CalcField2,
       (CalcField1 - CalcField2) / CalcField2 AS Percentage 
FROM 
    ( SELECT InvoiceNumber,
             Field1 + Field2 AS CalcField1,
             Field3 + Field4 AS CalcField2
      FROM ...
    ) AS x ;

The other option, is not to use a derived table at all but double the calculated expressions:

SELECT InvoiceNumber,
       Field1 + Field2 AS CalcField1,
       Field3 + Field4 AS CalcField2,
       ((Field1 + Field2) - (Field3 + Field4)) / (Field3 + Field4) 
         AS Percentage 
FROM ... ;