Common Table Expression (CTE)
A Common Table Expression (CTE) is a tool for keeping SQL queries simple. It creates a result set that exists only temporarily in a larger query.
What is a Common Table Expression (CTE)?
A Common Table Expression (CTE) works as a temporary result set that can be referenced by a subsequent SELECT statement in SQL. It allows you to save a piece of logic that can be referenced one or multiple times within a query.
Why use CTEs?
SQL queries can often become very long and complex. This is especially true if a query contains multiple nested queries. CTEs allow you to break this SQL code into smaller digestible chunks that make the code easier to read and understand. Each chunk is its own CTE, which can all be combined in a final SELECT statement.
In some cases, you might need to reference a piece of logic multiple times within a query. Defining this logic as a CTE means you can reference it multiple times, without having to repeat the code.
How can I use CTEs with Weld?
Weld’s data modelling feature keeps all of your models organized in a single space. To use CTEs in Weld, you can simply go to the model interface and start writing your query.
You start defining a CTE using the WITH clause. Follow this with a new line, give your CTE a descriptive name followed by AS, and then write your SELECT statement in parentheses.
Example using the Weld Sample Data
In this example, we’ve added pre-aggregated information about the ‘webshop_order_line’ table to the ‘webshop_order’ table. The nested query looks like this:
The same query can be written with a CTE like this:
Which one do you grasp the essence of the quickest? You might notice that the second version has 3 more rows (21 vs 18), but we’re not optimising for row count. Rows are cheap, what’s expensive is the brainpower it takes to read SQL queries.
The first version gets the job done, but the second version is more structured, and more straightforward to read and understand. It gives you a heads up — “here comes something about order lines per order” — and it becomes natural to join this to the ‘webshop_order’ table.
Say we also wanted to add pre-aggregated information about the 'webshop_order' table to the 'webshop_order' table itself. This can be done by adding another CTE:
How would this look without using CTEs?
Again, there are fewer rows with the nested approach, but the flow is not nearly as clear as in the CTE approach. A big reason for this is the order in which sub-queries are introduced. In CTEs, you’re gradually introduced to one (sub) query at the time, whereas in the above example you just get one long nested query that you have to break out into digestible chunks yourself.
Create and manage all of your data models in one place with Weld. Features like smart autocomplete, code folding, error highlighting, audit logs, and version control make writing models a breeze. Plus, an easily searchable library keeps things organized, even as you scale. Sign up to Weld and use the Weld Sample Data to explore CTEs yourself.