SQL SELECT statement expression value reuse for other expression

Question

I have a table with huge row count in mysql (though I am looking for generic SQL solution)

very_big_table(INT a, INT b, INT c, ...)

I wanted SELECT statement

SELECT a, 
    (b + c) as expression1,
    (b + c + a) AS expression2 -- basically (expression1 + a)
FROM very_big_table
WHERE ...
GROUP BY a
ORDER BY a DESC

this looks good and easily readable as long as the expression1 is simple.
But when CASE-WHEN/IFNULL()/SUM()/MIN()/STRCAT() or some Operator comes into play in these expressions its difficult to read and debug.

I have gone through some of the already asked questions
assigning mysql value to variable inline
Use value of a column for another column (SQL Server)?
How to use conditional columns values in the same select statement?

But if I use the approaches described something like

SELECT a, 
    expression1,
    (expression1 + a) AS expression2
FROM 
    (SELECT a,
        (b + c) AS expression1
    FROM very_big_table
    WHERE ...
    GROUP BY a) as inner_table
ORDER BY a DESC

this works fine, but this query is taking some 70x more time to execute. Atleast when i fired it, though only once.
what if I have multiple levels of the expressions in the output columns?

Is there any elegant way to deal with this, without compromising readability?

BTW why isnt this expression reuse or alias reference in select statement not supported by SQL standards or vendors? (supposing there are no cyclic evaluation in the single SELECT statement expressions. in that case the compiler fails)

Solution 1

You can use User-Defined Variable to solve your problem. Your SQL can be rewritten as:

SELECT a, 
    @expr1 := (b + c) as expression1,
    (@expr1 + a) AS expression2
FROM very_big_table
WHERE ...
GROUP BY a
ORDER BY a DESC

You can refer to this post.

Solution 2

I have 2 choices for that..

using your actual query as a subquery

 select a,exp1,exp1+a as exp2
 from (SELECT a 
             ,(b + c) as exp1
       FROM very_big_table
       WHERE ...
       GROUP BY a
 )V
 ORDER BY a DESC

or adding a outer apply statment for your query...

SELECT a 
      ,OA.exp1 as expression1
      ,(OA.exp1 + a) AS expression2 -- basically (expression1 + a)
FROM very_big_table
outer apply (select (b + c) as exp1) OA
WHERE ...
GROUP BY a
ORDER BY a DESC

I guess the second option, using outer apply, is better for read...

keep in mind that Outer apply runs for each row

so it may be a bad idea if the exp1 have to access large amounth of data from tables..

anyway, just using fields that you are already getting in your actual query you will not have a great cost by adding it.

so.. what way you would choose?

Comments

[Andriy M]

Why outer apply and not cross apply?