Introduction to Common Table Expressions
Structured Query Language (SQL) is a programming language used to access and manage data in relational databases. Common Table Expressions (CTE) is a feature of SQL that allows you to define a temporary named result set that can be referenced within a main query without the need for repeated code.
Essentially, CTEs act as virtual tables that are available only for the duration of a query.
CTEs are useful for several reasons, including improving query readability, breaking down complex queries into smaller, reusable pieces, and reducing the execution time of queries.
In this article, we will explore the definition and functionality of CTEs, along with examples of SQL queries that utilize CTEs.
Definition and Functionality
A Common Table Expression is a temporary named result set that can be accessed multiple times in a single query. It can be created using the
WITH keyword and can be used to enhance the readability and performance of SQL queries. CTEs can be thought of as virtual tables that are only available for the duration of a query and can be referenced by other queries within the main query.
In more technical terms, a CTE defines a query that can be treated like a table or a view. The CTE query is executed once and the results are stored in a temporary result set.
The main query then references that result set to build the final output.
Syntax and Examples
The syntax of CTEs consists of the
WITH keyword, followed by the name of the CTE query and the columns it returns. The main query references the CTE query by name.
The syntax for CTEs can be summarized as:
WITH CTE_Query_Name (Column1, Column2, …) AS (
CTE_Query
)
SELECT … FROM …
JOIN CTE_Query_Name ON … Here’s an example of how to use a CTE to create a report of employees who have worked at the company for over 10 years:
WITH TenYearEmployees (EmployeeID, FirstName, LastName, Tenure) AS (
SELECT
EmployeeID,
FirstName,
LastName,
DATEDIFF(YEAR, HireDate, GETDATE()) as Tenure
FROM Employees
WHERE
DATEDIFF(YEAR, HireDate, GETDATE()) >= 10
)
SELECT * FROM TenYearEmployees
In this example, we created a CTE named “TenYearEmployees” that contains the EmployeeID, FirstName, LastName, and Tenure (years of employment) for employees who have worked at the company for over 10 years. The main query then selects all columns from the “TenYearEmployees” CTE.
Examples of SQL Queries using Common Table Expressions
Example 1: Obtaining a report with the highest priced item sold
In this example, we will use a CTE to obtain a report showing the highest priced item sold:
WITH MaxSale (ItemName, SaleAmount) AS (
SELECT TOP 1
Items.ItemName,
MAX(Sales.SaleAmount)
FROM Sales
JOIN Items ON Sales.ItemID = Items.ItemID
GROUP BY Items.ItemName
)
SELECT * FROM MaxSale
In this example, we created a CTE named “MaxSale” that retrieves the highest sale amount for each item, then selects the item with the highest sale amount. The main query then selects all columns from the “MaxSale” CTE.
Example 2: Generating a report with the highest daily revenue by branch
In this example, we will use a CTE to generate a report of the highest daily revenue by branch:
WITH BranchRevenue (BranchID, Revenue, Date) AS (
SELECT
Sales.BranchID,
SUM(Sales.SaleAmount),
CAST(Sales.SaleDate AS Date)
FROM Sales
GROUP BY Sales.BranchID, CAST(Sales.SaleDate AS Date)
)
SELECT
Branches.BranchName,
BranchRevenue.Date,
MAX(BranchRevenue.Revenue) as HighestRevenue
FROM Branches
JOIN BranchRevenue ON Branches.BranchID = BranchRevenue.BranchID
GROUP BY BranchRevenue.Date, Branches.BranchName
In this example, we created a CTE named “BranchRevenue” that calculates the total sales revenue for each branch on each day. The main query then joins the “Branches” and “BranchRevenue” tables, grouping the results by date and branch name, and selecting the date and highest revenue for each date and branch.
Example 3: Organizing a long query using SQL CTEs
In this example, we will use multiple CTEs to organize a long query:
WITH
OrderedItems (ItemID, ItemName, Price) AS (
SELECT
Items.ItemID,
Items.ItemName,
Items.Price
FROM Items
ORDER BY Items.ItemName
),
TotalSales (ItemID, TotalSales) AS (
SELECT
Sales.ItemID,
SUM(Sales.Quantity)
FROM Sales
GROUP BY Sales.ItemID
),
Locations (LocationID, LocationName, ClientID) AS (
SELECT
Locations.LocationID,
Locations.LocationName,
Clients.ClientID
FROM Locations
JOIN Clients ON Clients.ClientID = Locations.ClientID
)
SELECT
OrderedItems.ItemName,
OrderedItems.Price,
TotalSales.TotalSales,
Locations.LocationName
FROM OrderedItems
JOIN TotalSales ON OrderedItems.ItemID = TotalSales.ItemID
JOIN Sales ON Sales.ItemID = TotalSales.ItemID
JOIN Locations ON Locations.LocationID = Sales.LocationID
AND Locations.ClientID = ‘ABC123’
In this example, we created three CTEs to organize a long query. The “OrderedItems” CTE orders the items by name, the “TotalSales” CTE calculates the total sales for each item, and the “Locations” CTE retrieves location information for a specific client.
The main query then joins the “OrderedItems”, “TotalSales”, “Sales”, and “Locations” tables, selecting the ordered item name, price, total sales, and location name for a specific client. Example 4: Obtaining a report with the largest ticket sold by branch
In this example, we will use a CTE to obtain a report showing the largest ticket sold by branch:
WITH LargestTicketByBranch (
BranchID,
SaleAmount,
RowNum
) AS (
SELECT
Sales.BranchID,
Sales.SaleAmount,
ROW_NUMBER() OVER(
PARTITION BY Sales.BranchID
ORDER BY Sales.SaleAmount DESC
) AS RowNum
FROM Sales
)
SELECT *
FROM LargestTicketByBranch
WHERE RowNum = 1
In this example, we created a CTE named “LargestTicketByBranch” that uses the ROW_NUMBER() function to calculate the row number for each sale partitioned by branch, and ordered by sale amount in descending order. The main query then selects all columns from the “LargestTicketByBranch” CTE where the row number is equal to one, thus selecting the largest ticket sold by each branch.
Example 5: Using a nested CTE to obtain items priced over $90 sold by London-2
In this example, we will use a nested CTE to obtain a report of items priced over $90 sold by London-2:
WITH
LocationSales (LocationID, ItemID) AS (
SELECT
LocationSales.LocationID,
LocationSales.ItemID
FROM Sales
INNER JOIN Locations ON Sales.LocationID = Locations.LocationID
WHERE Locations.LocationName = ‘London-2’
),
HighPricedItems (ItemID, ItemName, Price) AS (
SELECT
Items.ItemID,
Items.ItemName,
Items.Price
FROM Items
WHERE Items.Price > 90
)
SELECT
HighPricedItems.ItemName,
HighPricedItems.Price
FROM HighPricedItems
INNER JOIN (
SELECT DISTINCT ItemID
FROM LocationSales
) AS SoldItems ON HighPricedItems.ItemID = SoldItems.ItemID
In this example, we created a nested CTE to obtain a report of items priced over $90 sold by London-2. The “LocationSales” CTE retrieves all item sales for London-2, while the “HighPricedItems” CTE retrieves all items with a price over $90.
The main query then joins the “HighPricedItems” and “LocationSales” tables, selecting the item name and price for items sold in London-2.
Conclusion
Common Table Expressions are a useful feature of SQL that allows you to define temporary named result sets that can be reused within a query. CTEs improve query readability and performance and allow you to organize complex queries into smaller, reusable pieces.
The examples provided in this article demonstrate how CTEs can be used to obtain reports with the highest priced item sold, generate reports of the highest daily revenue by branch, organize long queries, obtain reports with the largest ticket sold by branch, and filter items priced over $90 sold by a specific location.
3) Recursive Queries and Common Table Expressions
Recursive queries are a subset of SQL that allows you to work with recursive data structures. It enables you to loop through and traverse through hierarchical data, making it a useful feature for dealing with trees, graphs, organizational data, and many other types of data structures that need to be traversed recursively.
The primary functionality of recursive queries is that they allow you to reference a non-recursive query term as an initial starting point and then join it with a recursive query term. The recursive query term is defined in a Common Table Expression (CTE) and refers back to itself in order to progress through each step of the recursive hierarchy.
Syntax and Examples
The syntax for recursive queries using Common Table Expressions is similar to that of CTEs, with the addition of the RECURSIVE keyword. Here is an example of how to use a recursive query to traverse a simple hierarchical structure:
WITH RECURSIVE EmployeeHierarchy (EmployeeID, EmployeeName, ManagerID, Level) AS (
SELECT EmployeeID, EmployeeName, ManagerID, 1 as Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT Employees.EmployeeID, Employees.EmployeeName, Employees.ManagerID, EmployeeHierarchy.Level + 1
FROM Employees
JOIN EmployeeHierarchy ON Employees.ManagerID = EmployeeHierarchy.EmployeeID
)
SELECT EmployeeName, Level
FROM EmployeeHierarchy
ORDER BY Level DESC;
In this example, we create a recursive CTE named “EmployeeHierarchy” that selects the top-level employees (i.e. those with no manager), and then joins them with their respective employees at the next level. The second part of the query joins the “Employees” table with the “EmployeeHierarchy” CTE and recursively references itself to traverse the hierarchy until all employees have been included.
The final part of the query selects the employee’s name and level and orders the result set by descending level.
Conclusion and Resources
Using Common Table Expressions allows for more readable SQL queries and presents many benefits, including breaking down complex queries and improving performance. The addition of recursive queries to CTEs makes it easier to manage and traverse hierarchical data structures.
If you want to learn more about recursive queries using CTEs, there are many online resources available. You can start by taking an online course such as the Recursive Queries course offered by Codeacademy, or tackle the Advanced SQL track that offers advanced CTE concepts and recursive data structures.
Moreover, for more comprehensive information on CTEs and recursive queries, you can refer to SQL interview questions, which provide insights into how these concepts are used in a real-world scenario. Additional online resources and forums also offer discussions on recursive SQL tree traversal and other complex CTE examples that can help you grasp and master this feature.
Common Table Expressions (CTEs) are a powerful feature of SQL that allow you to create temporary result sets that can be referenced in a main query. CTEs improve query readability and performance, and can be used to organize and breakdown complex queries.
Recursive queries are a subset of SQL that can work with recursive data structures through the use of CTEs. By using recursive queries with CTEs, you can easily traverse hierarchical data structures and graphs. Taking online courses and accessing online resources can help in mastering these concepts, which are widely used in real-world scenarios, including SQL interview questions and recursive SQL tree traversal.
The ability to understand and use CTEs and recursive queries provides valuable insights into managing and structuring data suited for many complex problems.