How does CTE performance compare to subqueries in complex SQL queries?

When it comes to querying and related techniques in RDBMS engines, the issue of performance is very important. Deciding whether to use a CTE or Sub Query depends on the specific query requirements, the RDBMS used, and the results of use case testing. To get the best out of each in a system, if the cost is acceptable, we can write queries in both formats and compare the performance of each. But in general there are the following rules: 1. CTE simplifies our queries. 2. CTE should always be materialized. 3. The differences between the two show themselves where the system is large and complex.

Theoretically the performance of a CTE vs a 'temp table' vs a subquery should not change much - the query optimizer should be able to figure out the best way to execute code, regardless. In real life it can make a difference how the query is written and the 'why' of this is simple: if a human can't read the query and figure out what is wanted, then neither can the optimizer! Simpler code lends itself to performance. I personally like CTE structure because you can read the code 'left to right' and 'top to bottom' - in other words, normally - and hopefully see what the intent is. Reading subqueries (aka 'nested' queries) can be a challenge because you are reading 'in to out' and 'bottom to top' - not easy to do in a complicated case.

I virtually always use CTE structure if I have to prep the data. For one, a CTE is far more readable. This is not a small consideration if you are handing this code to someone else to support - or if you will come back to it in six months and need to refresh yourself on what you were doing! Another plus is that you can 'cascade' the results from the first CTE into a second CTE, and so on, prepping the data as needed from step to step, until you execute the final 'select' statement that uses the final CTE as its source. Or -- you may have one source from which you need to pull two distinct sets of data and then compare them. Two CTEs will get this done nicely, where you again join them in the final 'select' to see the results.

Use CTEs on small datasets with complicated queries. For serious datasets, default to not using them. I have never met an optimizer that doesn't start to do stupid things when faced with CTE queries on 1B+ row tables.

It is extremely common for a query to get more complex over time. Often, a developer tasked with adding yet another wrinkle to an existing complex query decides to lift part of it into a CTE without considerations for performance (for example, leaving out any and all restrictive parameters); and this all works fine on the dev server, and completely craters prod because it table locks your main contention table.

Optimization in SQL is one of my favorite parts. You always learn new things and correcting and optimizing is continuous growth.