In my current workplace we've had queries like the following one for years that contain a correlated subquery that reference parts of our outer query:
select sum(ssbsect_enrl * a.scbcrse_credit_hr_low) as "Credits"
from ssbsect,
scbcrse a
where ssbsect_term_code = :term_code
and ssbsect_subj_code = a.scbcrse_subj_code
and ssbsect_crse_numb = a.scbcrse_crse_numb
and a.scbcrse_eff_term = (select max (b.scbcrse_eff_term)
from scbcrse b
where a.scbcrse_subj_code = b.scbcrse_subj_code
and a.scbcrse_crse_numb = b.scbcrse_crse_numb
and b.scbcrse_eff_term <= ssbsect_term_code);
These queries tend to run a bit slowly (the example above runs in about 4 seconds for a current term code value), but it's been difficult to find an alternative that would be more performant than the option above even though I've explored a few potential ideas.
Today I came across the slides by Markus Winand here:
SQL Slides by Markus Winand (0 Bytes)And the use of "LATERAL" mentioned at the beginning there seemed like a potential alternative to try out, but the example provided may be too simple for me to wrap my head around for the query above (either that, or LATERAL may not actually be a good use case for the particular type of correlated subquery we are using above).
In any case, I don't think myself or any of my colleagues have ever reached out to a forum resource like this one to come up with some outside knowledge/ideas that might help out, but if anybody has any thoughts on ways that we can potentially change our approach for these queries it would be appreciated!