But that first query is way bigger. Other than a speed boost, it may not clear why I think
the first query is better, so I thought I’d dig into it a bit more. I’d like to
explain more about why I think this is better (not just faster) and offer up
some alternative notation.
I think the biggest reason to avoid correlated subqueries is not the performance
benefit, it’s the cognitive load that it forces upon us. A big, gnarly query
with many correlated subqueries makes it difficult to reason about.
When you encounter a big query that’s doing more than you can take in at once,
what’s the first thing you do? You break it down. Start executing pieces of the
query to get an idea of the relations that are at play in your mind. Correlated
subqueries make that difficult because you cannot select a piece of the query and
run it individually. Especially in the case of PeopleSoft, these subqueries are
wired to other relations outside of the immediate scope.
Effective date subqueries also often get thrown into the WHERE clause
alongside conditions that represent real business logic, clouding the intention
of the query. The goal should be to shove all of this accidental complexity
aside, leaving the minimal amount of code to represent the core of what the query
is trying to accomplish.
The following query speaks for itself, doesn’t it? This should be our goal.
Any effective dated nonsense that this requires should take a back seat to this
Destructuring with WITH
Since we’re specifically talking PeopleSoft/Oracle here, we have the often
neglected WITH clause at our disposal. We can use WITH to destructure the
ugly peripheral bits into meaningful relation names.
In Clojure and other lisps, destructuring is done with the let form:
Oracle’s WITH clause is very similar:
WITH lets us nicely break up (and push aside) the non-core queries and clauses,
leaving the business logic to speak for itself.
So my point here is really composability. Regardless of whether you’re using
WITH clauses, building queries by composing discrete meaningfully-named
relations will help build clearer, faster, and more maintainable queries.