Why CTEs are key to your data analysis performance

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!

Subqueries vs. CTEs: The Friendly Showdown

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

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.

• They're powerful, able to perform complex operations
• Can become difficult to understand with multiple nesting levels
• If required more than once, they must be repeated in the query

CTEs

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.

• Assist in breaking down complex tasks into clear steps
• Highly readable, which helps make SQL easier to manage
• Defined once and can be reused multiple times in the same query

The reasons I pick CTEs over subqueries 9 times out 10

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:

• Temporary — CTEs are akin to temporary tables that can be easily defined and materialized during a work session. This allows you to cache results and avoid re-executing expensive or time-consuming computations.
• Reusable — CTEs can be seamlessly integrated into new queries, reducing the potential for errors and enhancing the reusability of your SQL code.

Husprey, the pedestal for CTEs

At Husprey, we've built two remarkable features to enhance the brilliance of CTEs even further: Local Tables and Query History.

Local tables: simplifying complexity

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:

• Cleaner SQL code — Now you have smaller, named queries that can be easily organized or proofread, speeding up your workflow. ‍
• Inspectable intermediate query results — With your query divided into smaller queries, you can review table results to visually confirm if the output matches your expectations. Plus, you can even chart it!

Query History: the logbook of your sql journey

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.