When To Use A CTE - And When Not To

Common Table Expressions

CTEs are a powerful feature of modern RDBMSs which allow you to do some very creative things with set-based data. Some systems even allow you to nest them inside of themselves for even more crazy, creative solutions. Let’s discuss when to use a CTE.

The word “common” from the acronym CTE (Common Table Expression) means you want to use a query more than once — because it’s common.

I see it all the time where people use a CTE simply because they can, they often feel it makes their code “prettier” — and they’re not necessarily wrong — but there’s a reason they exist and if I see a code with a CTE, my first assumption is it’s there for one of those reasons…

Take a look at some good examples of when I’ve used CTEs in this blog:

Good 🙂

Joining to a subquery more than once.
Recursively joining to a query.
Joining a subquery to itself.
Code abstraction when dealing with multiple subqueries, especially when may layers of nesting.

The word “common” from the acronym CTE (Common Table Expression) means you want to use a query more than once — it’s common. Therefore, by definition alone, you only want to use a CTE when it makes technical sense to do so.

Bad 🙁

Because you like them.
It makes the code “easier to read.”

I often see people use a CTE because they think it’s cool or that it “makes their code easier to read.” But the fact is, while it is indeed ‘cool’, it’s actually making your code harder to read. When I see a CTE being used, I immediately presume that the CTE is there for a legitimate, technical reason and therefore I should expect some sort of recursion or self-joining — totally negating the ‘easier’ part of the intent.