Common table expressions (CTEs) are handy tools for data analysts and are widely accepted as a way to avoid creating unnecessary tables and views. Most people using SQL have heard of common table expressions since they are valuable tools for running queries. However, if you’re just a beginner or brand new to SQL, you need to know how and when to use CTEs.
In this article we will be discussing the definition of common table expressions and how you can use them to simplify operations in SQL. You’ll learn why they are so helpful for data professionals and how to use them to overcome some annoying limitations of SQL. If you want to jump ahead, here is a clickable table of contents:
- What are common table expressions (CTEs)?
- How are common table expressions helpful?
- Common table expression types
- How to create a common table expression
- Conclusion
Common table expressions help coders and analysts make more sense of SQL operations without the limitations and redundancies that often hold new data analysts back when coding in SQL. If you are looking for a long-term career in data, you will be glad to learn these handy tricks for working with SQL using CTEs.
1. What are common table expressions (CTEs)?
A common table expression (CTE) is a temporary set of results defined by the scope of a statement. INSERT, SELECT, DELETE, UPDATE, and CREATE VIEW are all popular statement examples that can be used with CTEs. It’s not meant to replace other tables and operations, but it can be a useful tool for SQL users who need to work around common limitations.
Here is an example of a simple CTE:
WITH Employee_CTE (EmployeeNumber, Title)
AS
(SELECT NationalIDNumber,
JobTitle
FROM HumanResources.Employee)
SELECT EmployeeNumber,
Title
FROM Employee_CTEIn this example, you can see that the contained SQL is actually a query. This query will offer a result in the form of a table that matches up national ID numbers with the names of HR employees.
You’ll notice that a CTE is similar to a derived table. It only lasts for the duration of the query and is not stored as an object. However, there is a key difference between CTEs and derived tables. CTEs, unlike derived tables, can be self-referencing and even be referenced numerous times for the same query.
2. How are common table expressions helpful?
CTEs enable users to write and maintain complex queries fast and easily. For aspiring SQL programmers, CTEs can greatly increase data processing speed. It reduces the complexity of common queries by increasing their readability. This is achieved by deconstructing typically complex queries into simple blocks that can be used and reused to write a query.
MySQL currently powers 9 out of 10 websites on the internet today, and using a SQL CTE over other common methods can be helpful in several ways:
Readability
One of the reasons why many data professionals turn to CTEs is because they promote readability. Instead of combining a query logic into a single cumbersome query, several CTEs can be created and later combined for an easy-to-read view of results. That way, you can get all the data necessary, then combine them in a final SELECT.
Substitute for a view
Another reason coders use CTEs is to substitute for another view. This can be particularly helpful if a user doesn’t have the permissions necessary to make a view object or if it would be a waste of time to make a view object for one-time use.
Recursion
Although we won’t discuss it much in this article, recursion is an important aspect of many queries that organize hierarchical data like organization charts. CTEs can help create recursive queries to have handy for data projects.
Overcome limitations
Like any other technology, SQL does have a few limitations when it comes to the SELECT statement. There are many ways to simplify SQL projects, and using CTEs is one of them. You can use CTEs to overcome limitations like self-referencing functions to perform GROUP BY statements.
Replacing other SQL objects with CTE
You can also use CTEs to replace other SQL objects like subqueries. Although CTEs and subqueries are very similar, CTEs have more capabilities than subqueries.
Some example use cases for using a CTE over other SQL objects include:
- When you need to reference a derived table more than once within a single query
- When you prefer an alternative method to create a view within a database
- When you need to perform the same calculation several times across query components
3. Common table expression types
There are two types of common table expressions, recursive and non-recursive CTEs. While recursion and non-recursion are complicated topics to grasp, there is a simple way to understand the differences in how they work.
Recursive CTE
A recursive CTE is a common table expression that references itself. A recursive query is a query that calls itself and is repeatedly run on a single subset of data. At one point, the recursive query will reach its end condition as it will not repeat ad infinitum. Once the recursion slows down, the CTE table will collect data after reviewing every successive result. This type of CTE is often used for organizing hierarchical data.
Non-recursive CTE
Unlike recursive CTEs, non-recursive CTEs do not use recursion. Since they don’t use recursion, they are typically easier to comprehend, especially for green SQL users. This type of CTE is often used as an alternative way of viewing data instead of using derived tables and views.
4. How to create a common table expression
For today’s article, we will discuss how to create non-recursive CTEs before moving on to more advanced recursive CTEs.
Steps to create a common table expression
Here are the basic steps involved with creating a non-recursive CTE:
- Get started with an SQL common table expression “WITH.”
- Next, provide a name for the result of your query.
- Once you’ve assigned a name to the result, follow with “AS.”
- Optionally, you can choose to specify column names to make your results easy to read and understand.
- Now it’s time to define the query to produce a set of desired results.
- Some situations require more than one CTE. If your project requires multiple CTEs, you should initiate each of the following expressions with a comma. Then repeat steps 2-4 for each.
- Finally, you can reference the above CTEs in subsequent queries.
CTE example
Here’s a simple example of the syntax involved in creating a non-recursive CTE:
WITH TableExpressionName (Column1, Column2, …, ColumnN)
AS
(Query Definition)In this example, the CTE has two main components. The first names the common table expression and defines the columns within it. The second defines the query in the form of a SELECT statement.
It’s important to remember that you can’t use ORDER BY, INTO, OPTION, or FOR BROWSE to define a query using non-recursive CTEs.
CTE example with two CTEs
Let’s take a look at an example using two CTEs, how to define them, and how to connect them with INNER JOIN:
WITH PersonCTE (BusinessEntityID, FirstName, LastName)
AS (SELECT Person.BusinessEntityID,
FirstName,
LastName
FROM Person.Person
WHERE LastName LIKE 'C%'),
PhoneCTE (BusinessEntityID, PhoneNumber)
AS (SELECT BusinessEntityID,
PhoneNumber
FROM Person.PersonPhone)
SELECT FirstName,
LastName,
PhoneNumber
FROM PersonCTE
INNER JOIN
PhoneCTE
ON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;In this example, the SELECT statement shows that the CTEs are joined like derived tables. CTEs can help separate operations for more complicated queries. This helps simplify the final query and makes it easy to keep track of results.
5. Conclusion
Now you know why so many data analysts swear by common table expressions. CTEs simplify queries and allow users to overcome limitations associated with coding in SQL.
When it comes to analyzing both complex and simple data sets, there are always multiple ways to achieve the same result. It’s just a matter of discovering the correct processes to deliver the best results within the shortest possible time without compromising on the end result.
Common table expressions help teams code faster, better, and more accurately so that mistakes do not cause bottlenecks in delivering crucial analytics to power your projects.
Interested in learning more about common table expressions and data analytics in general? Why not try out our free, 5-day data analytics short course?
You may also be interested in the following articles: