I taught myself SQL on a need-to-know basis. One thing that blew my mind and really helped making certain non-trivial queries more manageable was when I realized I could join subqueries. It never occurred to me to try, until one day I did and it worked.
A contrived example for illustration:
select oh.oid, oh.order_ref, ol.num_lines, ol.total_value
from order_head oh
join (
select oid, count() as num_lines, sum(value) as total_value
from order_lines
where status = 'ready'
group by oid
) ol on ol.oid = oh.oid
where total_value > 500
For certain queries this has led to huge speed-ups, by allowing rows to be filtered sooner rather than post-massive-join. For others it makes it easier to integrate with libraries, as aggregate fields like in the example can be filtered using the main where clause rather than in a having clause.
Yep, I've used this with huge success in PostgreSQL when needing to join the contents of tables containing potentially millions of records that could (usually) then be filtered via search criteria. Basically I'd do my search-filtering first via a sub-query, then simply join the result of that vs doing it in the opposite order. The performance gains were orders of magnitude.
A contrived example for illustration:
For certain queries this has led to huge speed-ups, by allowing rows to be filtered sooner rather than post-massive-join. For others it makes it easier to integrate with libraries, as aggregate fields like in the example can be filtered using the main where clause rather than in a having clause.