What is a CTE and when do you use them?
A Common Table Expression, or CTE, is a way to create a temporary, named result set within a SQL query. Think of it as a short-lived, virtual table that exists just for the duration of your query — a useful building block that helps structure and simplify your logic.
Rather than repeating complex subqueries in multiple places (inside JOINs, WHERE clauses or SELECT statements), you can define a CTE once and refer back to it throughout your query. This makes your SQL easier to read, debug, and maintain — especially as queries grow in size and complexity.
CTEs are ideal when you’re:
• Breaking a transformation into several logical steps
• Writing recursive queries
• Trying to keep your SQL clean and well-structured
CTEs vs Subqueries
If subqueries can do the same job, why use CTEs?
In many cases, they are interchangeable in terms of output — but CTEs offer a big advantage when it comes to readability and maintainability.
Subqueries are typically embedded inline within FROM or WHERE clauses, which can lead to tangled, hard-to-read SQL – particularly when dealing with multiple joins or nested logic.
CTEs, on the other hand, allow you to define each intermediate step at the top of your query, and refer to them by name throughout. It’s a more modular, structured approach — making your code easier to follow, extend, and debug.
Imagine you’re calculating a rate metric that requires several joins and filtering steps.
Using subqueries, you’d likely end up with multiple layers of nesting — difficult to interpret and a pain to troubleshoot.
With CTEs, you can split the logic into clearly named steps, each handling a specific part of the process. The result is a query that’s much easier to reason about and modify later.
The right tool for the job.
At Bright Analytics we use CTEs and Sub Queries in different ways depending on the challenge we’re looking to solve, and the platform supports them both. CTEs typically used in our ELT processes when something complex is needed and Sub Queries tend to be used to create completely custom data sources in our semantic layer for reporting on.
If you’re looking for news ways to work with tricky data and want discuss some ideas and approaches we’d love to hear from you.
Get email updates
More insights from Bright Analytics
Start solving your data challenges today
Our self service platform eats complexity for breakfast. With our team of friendly data specialists on hand to help, we will have a solution perfectly tailored to your needs up and running in under a week.
Get In Touch