For those among you who write sql, you'll know it often starts with the simple 'SELECT FROM WHERE.' Soon enough, it progresses to 'SELECT FROM JOIN WHERE,' and not long after that, you'll likely feel the pull towards either subqueries or Common Table Expressions (CTEs).
The debate between these two has been longstanding, and perhaps will always spark a bit of controversy. Performance used to be the primary battlefield, but over time, query compilers have become more adept at optimizing the executed code, reducing this concern. In most use cases, performance should not be the primary factor when choosing between the two. (Until it does).
Let's explore further into the realms of subqueries and CTEs!
When dealing with advanced sql needs, subqueries and Common Table Expressions (CTEs) often come to the rescue. Both help us handle the complexity, but they do so in different ways.
Subqueries are like nested compartments within your SQL queries.
They're powerful and can perform operations that might otherwise need several sequential steps. However, the more you nest those, the harder your sql becomes to understand and debug. It's like trying to follow the plot of a movie with too many flashbacks - it can get confusing fast.
On the other hand, we have CTEs.
CTEs allow you to sequentially lay out each step of the query, making your sql easier to parse, like a well-structured document.
Also, you only need to define your CTE once, and then you can use it multiple times in your query. That means less repetition and fewer opportunities for errors to sneak in.
While both subqueries and CTEs have their utility in sql, I find myself favoring CTEs. Why?
In the world of coding, there's a saying that we don't write code for machines to interpret our intentions, but also for the future developer who might need to edit (or debug) your code.
sql is similar to and should be considered as code.
Your sql queries should become reusable assets within your team, be it for your future self, the newcomer joining the team, or a teammate assisting you during a specific investigation.
At least this is my take.
CTEs bring several additional benefits to the table:
At Husprey, we've built two remarkable features to enhance the brilliance of CTEs even further: Local Tables and Query History.
Local tables offer a way to decompose a complex query into smaller, manageable ones that can be collectively referenced. Behind the scenes, we group your queries to execute your SQL, leveraging the full potential of CTEs. This approach yields multiple benefits, but two stand out in particular:
Your local tables split and named, they all show up in your Query History. This means you're just a search away from locating the "function" (actually a local table) you need, leveraging the previous work of the team. This feature transforms team collaboration, even if you're a team of one! It helps you rediscover work done six months ago, not just the SQL but all the surrounding context too. As a result, your investigations or ad-hoc analyses become faster than ever.
Splitting your SQL and naming local tables are the initial steps towards stellar documentation. But sometimes, you may need to comment on specific lines in your queries or seek explanations about what a specific query does. This is precisely what we're working on with our newest GenAI capabilities.
Reach out if you what to try these capabilities first.