Introduction
SQL CTE (Common Table Expression) is a powerful feature in SQL that allows you to define temporary named result sets that can be used within the context of a single SQL statement. CTEs are similar to subqueries but offer some key advantages, including better readability and performance.
CTEs can be used in a variety of scenarios, such as when you need to perform complex data transformations or when you need to reuse the same subquery multiple times within a single SQL statement. In this article, we'll explore some interesting examples of how to use SQL CTEs to perform some common tasks.
Example 1: Recursive Queries
One of the most common use cases for CTEs is to perform recursive queries. Recursive queries are queries that involve self-referencing tables or hierarchies. Let's say you have a table called "employees" that contains information about employees, including their names, titles, and managers. You can use a CTE to retrieve the hierarchy of all employees in the organization, starting from the CEO.
Here's an example of how to use a CTE to retrieve the hierarchy of all employees:
WITH EmployeeHierarchy AS (
SELECT EmployeeID, Name, Title, ManagerID
FROM employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.Name, e.Title, e.ManagerID
FROM employees e
INNER JOIN EmployeeHierarchy eh ON eh.EmployeeID = e.ManagerID
)
SELECT *
FROM EmployeeHierarchy
In this example, the CTE is named "EmployeeHierarchy" and contains two SELECT statements. The first SELECT statement retrieves the CEO (the employee with no manager), and the second retrieves all employees who report to the CEO. The UNION ALL operator combines the results of the two SELECT statements into a single result set.
Example 2: Calculating Running Totals
Another common use case for CTEs is to calculate running totals. Running totals are cumulative sums that are calculated over a set of rows in a table. Let's say you have a table called "sales" that contains information about sales transactions, including the transaction date and the sale amount. You can use a CTE to calculate the running total of sales for each day.
Here's an example of how to use a CTE to calculate the running total of sales:
WITH SalesTotal AS (
SELECT TransactionDate, SUM(Amount) AS Total
FROM sales
GROUP BY TransactionDate
)
SELECT TransactionDate, Total,
SUM(Total) OVER (ORDER BY TransactionDate) AS RunningTotal
FROM SalesTotal
In this example, the CTE is named "SalesTotal" and contains a single SELECT statement that calculates the total sales for each day. The main SELECT statement retrieves the TransactionDate and Total columns from the CTE, and it also calculates the running total using the SUM and OVER functions.
Example 3: Removing Duplicates
Another useful feature of CTEs is their ability to remove duplicates from a result set. Let's say you have a table called "orders" that contains information about orders, including the order number, customer ID, and order date. You can use a CTE to remove duplicate orders the customer placed on the same day.
Here's an example of how to use a CTE to remove duplicate orders:
WITH OrderCTE AS (
SELECT OrderNumber, CustomerID, OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID, OrderDate ORDER BY OrderNumber) AS RowNumber
FROM orders
)
SELECT OrderNumber, CustomerID, OrderDate
FROM OrderCTE
WHERE RowNumber = 1
In this example, the CTE is named "OrderCTE" and contains a single SELECT statement that retrieves the order number, customer ID, and order date from the "orders" table. The SELECT statement also includes the ROW_NUMBER function, which assigns a unique number to each row within each partition. In this case, the partition is defined by the combination of CustomerID and OrderDate. The ORDER BY clause specifies the order in which the row numbers are assigned.
The main SELECT statement retrieves the OrderNumber, CustomerID, and OrderDate columns from the CTE, and it also includes a WHERE clause that filters out all rows except for the first row for each customer on each day. This effectively removes all duplicate orders and returns only the first order placed by each customer on each day.
Conclusion
CTEs are a powerful feature in SQL that can help you perform complex data transformations and improve the readability and performance of your SQL queries. In this article, we've explored some interesting examples of how to use CTEs to perform recursive queries, calculate running totals, and remove duplicates from result sets. By mastering CTEs, you can take your SQL skills to the next level and become a more efficient and effective SQL developer.