This content originally appeared on Level Up Coding - Medium and was authored by David Sands
Advanced SQL — CTEs
Common Table Expressions using the WITH clause.
When writing out SQL queries, it is easy for things to get out of hand or too complex to the point where readability is drastically reduced. Thankfully, in 2005, CTEs (common table expressions) were introduced into SQL Server, granting us the ability to assign a name to a set of results that can be utilized within some other CRUD statement. In this blog post, I want to briefly introduce CTE syntax and how to use it. I hope to write another blog post going into more detail on complex use cases in the future.
What is a CTE?
As stated above, a CTE (sometimes referred to as a “WITH statement”) is a temporary named result set that is not saved anywhere, it only exists in memory while the query is being run (unlike a temp table which exists in a temp DB file). The results of a CTE only exist within the execution scope of a specific statement; in other words, the results are only available to the CRUD statement immediately following the WITH clause in which the CTE is defined. CTEs are a great way to simplify and manage complex queries, making your code easier to read by breaking it into sections.
CTE syntax
I have written a simple SQL query as an example to showcase how a CTE can be used:
The query above will return the following results based on the sample data that I got from sqlservertutorial.net:
The first thing to note about CTE syntax is that it is defined within a WITH clause directly preceding the CRUD statement in which its results are intended to be used. The basic syntax is as follows:
WITH CTE-name AS (CTE-definition)
You can also define multiple CTEs by separating the definitions using commas:
WITH name AS (definition),
second-name AS (second-definition)
In order to define a CTE, you simply write a SELECT statement as your parenthetical text. This statement should capture any data that you desire to use within another query. Once you have defined your CTE, it can now be referenced in your final query:
WITH name AS (definition)
SELECT * FROM name
Lets take another look at the example query above:
The section highlighted in green is the WITH clause where the CTE is defined and the section highlighted in blue is the SELECT statement that is querying off of the CTE. This is a great setup because the CTE can handle most of the hard work for us and we can edit our final query based on precisely what we need from this data set.
Common use cases
A CTE is an incredibly useful and flexible tool to add to your SQL repertoire. Here are a couple of the most common use cases:
- CTEs are often used to increase the readability and manageability of queries. By defining multiple CTEs, you can break up a long, complex query into chunks of logic that can be referenced in your final query. They are also a great way to follow the DRY principle if you are referencing the same data in multiple subqueries.
- CTEs can be used to create recursive queries, which is helpful as a normal SELECT statement cannot reference itself. See Microsoft’s documentation on recursive queries using CTEs for more information on this.
TL;DR
- A CTE exists only in the execution scope of the subsequent statement.
- Unlike a temp table, CTEs are not saved anywhere.
- A CTE is defined by writing a SELECT statement in parentheses following a WITH clause.
- A CTE can be referenced like any other table following the FROM clause in a query.
- CTE’s are a great way to DRY up code, increase readability and manageability, and write recursive queries.
Advanced SQL — CTEs was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by David Sands
David Sands | Sciencx (2022-04-05T01:07:22+00:00) Advanced SQL — CTEs. Retrieved from https://www.scien.cx/2022/04/05/advanced-sql-ctes/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.