Every story and comment from the Hacker News API, in a layout borrowed from 2001.
A comment on For Want of a JOIN. Parent comment
Simplest is to use WITH clauses (common table expressions/CTEs). They can help readability a lot and add a degree of composability within a query.
Second, you can split one query into several, each of which creates a temporary (or intermediate physical) table.
Third, you can define intermediate meaningful bits of logic as views to encapsulate/hide the logic from parents. For performance you materialize them.
Fourth, you can create stored procedures which return a result set and like any procedural or functional language chain or nest them.
These techniques are available in most databases. More mature databases support forms of recursion for CTEs or stored procedures or dynamic SQL.
As with most programming, proper decomposition and naming helps a fair bit.
There are no comments on this comment yet.
Simplest is to use WITH clauses (common table expressions/CTEs). They can help readability a lot and add a degree of composability within a query.
Second, you can split one query into several, each of which creates a temporary (or intermediate physical) table.
Third, you can define intermediate meaningful bits of logic as views to encapsulate/hide the logic from parents. For performance you materialize them.
Fourth, you can create stored procedures which return a result set and like any procedural or functional language chain or nest them.
These techniques are available in most databases. More mature databases support forms of recursion for CTEs or stored procedures or dynamic SQL.
As with most programming, proper decomposition and naming helps a fair bit.