Modularizing subselect definitions within a SELECT?

Question

I have a SELECT along the lines of

SELECT  
(very complex calc1) as `amount`,
(very complex calc2) as `paid_onlne`,
(very complex calc3) as `paid_by_check`,
(very complex calc1) - (very complex calc2)-(very complex calc3) as `amount_due`

FROM  billings
GROUP BY billings.id

All the calculations use one-many sub_selects

This has been asked before, but I am looking for a way to be able reuse the calc specifications 1,2,3 in subsequent columns without having to paste the code over and over - leaving a query that is very difficult to maintain.

I doubt that doing so would aid performance - but you can just bundle it all up as a subquery — Strawberry, May 30 '20 at 12:55
The purpose is not to improve performance but to make editing easier. Calc1 in the real app used in 4 different columns - so if a change is needed I have to do it 4 times. Each calculation is in fact a sub-query already so I am not sure what you mean by ‘bundle it up as a subquery.’ ? — Oliver G. Reid, May 30 '20 at 16:05
I think without knowing how the GROUP BY fits with the rest of the query, we cannot answer definitively, but I like LS's idea. — Strawberry, May 30 '20 at 17:25

Lukasz Szozda · Answer 1 · 2020-05-30T16:43:08.420

"The purpose is not to improve performance but to make editing easier. Calc1 in the real app used in 4 different columns - so if a change is needed I have to do it 4 times.

Yes it is possible with LATERAL JOIN - MySQL 8.0.14:

SELECT billings.id, 
 s1.amount,
 s2.paid_onlne,
 s3.paid_by_check,
 s1.amount - s2.paid_online - s3.paid_by_check as `amount_due`
FROM  billings
,LATERAL JOIN (very complex calc1) s1
,LATERAL JOIN (very complex calc2) s2
,LATERAL JOIN (very complex calc3) s3
GROUP BY billings.id;

Related: PostgreSQL: using a calculated column in the same query

Modularizing subselect definitions within a SELECT?

1 Answers1