Introduction – What is a CTE in SQL?
CTEs in SQL are a way to simplify code by creating a temporary dataset, giving it a name, then referencing that dataset later in your query
You can use Common Table Expressions (CTEs) as temporary named result sets within a SELECT, INSERT, UPDATE, or DELETE statement.
You may also have heard of them described as a WITH clause in SQL. The WITH clause defines and allows the use of them to simplify complex queries and improve their readability.
A CTE is created by first defining a query that generates a result set, and then referencing that result set in the WITH clause. You can use the result set in the main query as if it were a table.
An example of how to use a CTE is below:
Syntax of a CTE in SQL
The syntax of the WITH clause begins with the WITH statement, followed by the name of the temporary data set, in this case ‘Merged’
WITH Merged as
Next, the SQL code for that dataset is contained within a pair of brackets
We have used a simple UNION query as an example
SELECT [Product_ID], [Store_ID], [qty_sold] from SalesAunion
SELECT [Product_ID], [Store_ID], [qty_sold] from SalesB
You can find out more about UNION in SQL by following this link
You can add other CTE data sets too, by adding them to the first section of your SQL statement
The final step is to use the ‘Merged’ data set in the same way you would any other dataset
SELECT * from Merged
Putting this together gives us this code blockWITH Merged as (SELECT [Product_ID], [Store_ID], [qty_sold] from SalesAunion SELECT [Product_ID], [Store_ID], [qty_sold] from SalesB) SELECT * from Merged
and we can see the results in our SQL editor
Reasons to Use CTEs in SQL
-
Simplicity: CTEs can simplify complex queries and improve their readability.
-
Performance: CTEs can improve query performance by breaking down complex queries into smaller, more manageable parts.
Limitations of CTEs in SQL
-
Limited Scope: CTEs are limited in scope and can only be utilized within a SELECT, INSERT, UPDATE, or DELETE statement
-
Memory Usage: CTEs can consume a lot of memory, especially when dealing with large datasets. Although you could apply filters within your where clause to mitigate this issue. e.g. instead of returning 15 years of data, just bring back the last 2 years rolling?
-
Performance: CTEs can sometimes be slower than other methods, such as temporary tables or table variables, when dealing with large datasets.
Recursive Queries
People use CTEs for different reasons. They simplify complex queries and improve query performance. CTEs also make queries easier to read. They are also useful for breaking down complex queries into smaller, more manageable parts.
WITH CTE AS
( SELECT cast(GETDATE()AS date) AS [date]
UNION ALL
SELECT DATEADD(dd, 1, [date])
FROM CTE
WHERE DATEADD(dd, 10, [date])
< CAST(‘2023-12-31’ as date)
)
SELECT [date] FROM CTE
Running this produces a list of dates between the current date and the last date specified in the where clause
Conclusion
CTEs are used for a variety of reasons, including simplifying complex queries, improving query performance, and making queries more readable. They are also useful for breaking down complex queries into smaller, more manageable parts.
Now you know how to use a CTE in SQL
Contact us if you want to find out more or discuss references from our clients.
Find out about our Business Intelligence Consultancy Service.
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
Blog Posted by David Laws
