70.114
Simplifying Queries with Common Table Expressions (CTE)

Introduction

A Common Table Expression (CTE) in SQL is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can improve code readability, simplify complex queries, and provide a way to modularize query logic. They are defined using the WITH keyword, followed by the CTE name and its definition.

A CTE is basically a temporary view defined within a query’s scope. Unlike database views, which are persistent database object (although generally not materialized until referenced) and reusable across sessions, CTEs are local to the query they are defined in and exist only for the duration of that query. The general syntax of a CTE is:

WITH cte_name (optional_column_names) AS (
    -- CTE definition (usually a SELECT query)
    SELECT ...
)
-- Main query referencing the CTE
SELECT ... FROM cte_name;

Key Benefits of CTEs

CTEs are most commonly used in the following ways:

1. Improving Query Readability: By breaking a complex query into smaller, more manageable pieces, CTEs make the logic easier to follow.

   Example:

   WITH SalesByRegion AS (
       SELECT Region, SUM(Sales) AS TotalSales
       FROM SalesData
       GROUP BY Region
   )
   SELECT Region, TotalSales
   FROM SalesByRegion
   WHERE TotalSales > 100000;

   Here, the SalesByRegion CTE simplifies the process of filtering regions with high sales.

2. Recursive Queries: CTEs support recursion, making them essential for hierarchical data, such as organizational structures or tree-like data models.

   Example:

   WITH RECURSIVE OrgHierarchy AS (
       SELECT EmployeeID, ManagerID, EmployeeName, 1 AS Level
       FROM Employees
       WHERE ManagerID IS NULL
       UNION ALL
       SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, oh.Level + 1
       FROM Employees e
       INNER JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
   )
   SELECT * FROM OrgHierarchy;

   This query builds a hierarchy of employees and their levels in the organization.

3. Complex Joins and Subqueries: Instead of writing nested subqueries, CTEs allow you to name intermediate steps, improving modularity.

4. Multiple References: A CTE can be referenced multiple times within the same query, avoiding the need to repeat code and potentially improving performance.

CTEs are particularly useful in the following scenarios:

• Readability: When a query involves multiple subqueries or intricate logic, using CTEs can make the SQL easier to understand and maintain.
• Recursion: Recursive CTEs are essential for traversing hierarchical or tree-like data, such as file systems, family trees, or organizational structures.
• Reusable Logic: If you need to reuse a complex query multiple times within the same SQL statement, a CTE eliminates repetition.
• Debugging: During query development, defining parts of a query as CTEs makes it easier to test and debug individual components.

Necessity of CTEs

CTEs are not strictly necessary because the same functionality can generally be achieved using subqueries or temporary tables. However, they offer the following advantages:

1. Clarity and Maintainability: By breaking down complex queries, CTEs make SQL more human-readable.
2. Ad-Hoc Development: CTEs allow you to create temporary structures without modifying the database schema, which is useful for one-off analysis or rapid prototyping.
3. Performance Considerations: In some cases, using a CTE instead of a subquery can improve performance, though this depends on the database system and query optimizer.

That said, CTEs may not always be the best choice. For instance, if performance is critical and a query references the same CTE multiple times, the database’s query engine might execute the CTE’s logic repeatedly unless the optimizer materializes it. In such situations, temporary tables might be a better choice when dealing with large intermediate datasets because they are explicitly materialized and stored and thus are not repeatedly materialized.

Nevertheless, CTEs are an essential mechanism in SQL, improving code readability, modularity, and enabling recursion. While not strictly necessary for all queries, they are indispensable for many complex scenarios. The choice between using a CTE, a subquery, or a temporary table depends on the specific requirements of the query, including readability, performance, and maintainability.

CTEs in SQLite

SQLite supports Common Table Expressions (CTEs), including both standard and recursive ones. Below are some examples demonstrating their use in SQLite. We will use the sample database created below:

library(RSQLite)
db <- dbConnect(RSQLite::SQLite(), "sampleDB.db")

1. Simplifying Queries with Standard CTEs

Suppose you have a table named Orders with the following structure:

CREATE TABLE Orders (
    OrderID INTEGER,
    CustomerID INTEGER,
    OrderDate TEXT,
    TotalAmount REAL
);

You want to find the total amount spent by each customer in a specific year (e.g., 2024) and filter those who spent more than $5000.

Using a CTE, this query can be written as:

WITH CustomerTotals AS (
    SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
    FROM Orders
    WHERE strftime('%Y', OrderDate) = '2024'
    GROUP BY CustomerID
)
SELECT CustomerID, TotalSpent
FROM CustomerTotals
WHERE TotalSpent > 5000;

• Explanation: The CTE CustomerTotals calculates the total amount spent by each customer in 2024. The main query then filters for customers who spent more than $5000.

---

2. Recursive CTE for Hierarchical Data

Consider an Employees table with the following structure:

CREATE TABLE Employees (
    EmployeeID INTEGER,
    ManagerID INTEGER,
    Name TEXT
);

You want to list all employees in the hierarchy starting from a specific manager (e.g., ManagerID = 1).

Using a recursive CTE:

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, Name, 1 AS Level
    FROM Employees
    WHERE ManagerID = 1  -- Start from the specified manager
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, ManagerID, Name, Level
FROM EmployeeHierarchy;

• Explanation:
  • The anchor part of the CTE selects employees directly managed by ManagerID = 1.
  • The recursive part retrieves employees managed by those employees, repeating this process to traverse the hierarchy.
  • The Level column indicates the depth of each employee in the hierarchy.

---

3. Reusing Logic for Multiple References

Suppose you have a table named Products with the following structure:

CREATE TABLE Products (
    ProductID INTEGER,
    CategoryID INTEGER,
    Price REAL
);

You want to calculate the average price of products per category and find products that are above the average price in their category.

Using a CTE:

WITH CategoryAverages AS (
    SELECT CategoryID, AVG(Price) AS AvgPrice
    FROM Products
    GROUP BY CategoryID
)
SELECT p.ProductID, p.CategoryID, p.Price, ca.AvgPrice
FROM Products p
JOIN CategoryAverages ca ON p.CategoryID = ca.CategoryID
WHERE p.Price > ca.AvgPrice;

• Explanation:
  • The CTE CategoryAverages calculates the average price for each category.
  • The main query joins this CTE with the Products table to filter products priced above their category’s average.

---

4. Generating Sequential Numbers (Recursive CTE)

SQLite does not have a built-in function to generate sequences, but a recursive CTE can be used to create one.

For example, generate numbers from 1 to 10:

WITH RECURSIVE Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1
    FROM Numbers
    WHERE n < 10
)
SELECT n
FROM Numbers;

• Explanation:
  • The anchor part initializes the sequence with 1.
  • The recursive part increments n by 1 until it reaches 10.

---

5. Combining Multiple CTEs

SQLite allows defining multiple CTEs in a query. For example, consider the Orders table again, and you want to calculate: 1. The total revenue for 2024. 2. The total number of orders for each customer in 2024.

WITH TotalRevenue AS (
    SELECT SUM(TotalAmount) AS Revenue2024
    FROM Orders
    WHERE strftime('%Y', OrderDate) = '2024'
),
CustomerOrders AS (
    SELECT CustomerID, COUNT(OrderID) AS OrderCount
    FROM Orders
    WHERE strftime('%Y', OrderDate) = '2024'
    GROUP BY CustomerID
)
SELECT co.CustomerID, co.OrderCount, tr.Revenue2024
FROM CustomerOrders co
CROSS JOIN TotalRevenue tr;

• Explanation:
  • The TotalRevenue CTE calculates the overall revenue for 2024.
  • The CustomerOrders CTE calculates the number of orders for each customer in 2024.
  • The main query combines these results, showing the number of orders per customer along with the total revenue.

---

Why Use CTEs in SQLite?

CTEs in SQLite: - Enhance readability by breaking down complex queries. - Enable recursion for hierarchical data or sequence generation. - Allow reusable query components, avoiding duplication.

While not always necessary, CTEs can simplify logic and make queries easier to maintain. However, for very large datasets or performance-critical applications, testing the impact of CTEs versus subqueries or temporary tables is important since SQLite handles CTEs as inline query fragments rather than materialized views.

Example CTE Queries

Given the table definition and the inserted data:

CREATE TABLE T (a INT, b INT);

INSERT INTO T VALUES (11, 20), (10, 20), (10, 20), (10, 20), (88, 77);

The table T now contains the following data:

a	b
11	20
10	20
10	20
10	20
88	77

---

1. Query: Selecting All Data

To view all rows in the table:

SELECT * FROM T;

Result: | a | b | |—-|—-| | 11 | 20 | | 10 | 20 | | 10 | 20 | | 10 | 20 | | 88 | 77 |

---

2. Query: Using a Common Table Expression (CTE) to Remove Duplicates

You might want to eliminate duplicate rows, which SQLite can achieve with the DISTINCT keyword. Using a CTE:

WITH DistinctRows AS (
    SELECT DISTINCT a, b
    FROM T
)
SELECT * 
FROM DistinctRows;

Result: | a | b | |—-|—-| | 11 | 20 | | 10 | 20 | | 88 | 77 |

---

3. Query: Count Occurrences of Each Row

To count how many times each pair (a, b) appears in the table:

WITH RowCounts AS (
    SELECT a, b, COUNT(*) AS Count
    FROM T
    GROUP BY a, b
)
SELECT *
FROM RowCounts;

Result: | a | b | Count | |—-|—-|——-| | 11 | 20 | 1 | | 10 | 20 | 3 | | 88 | 77 | 1 |

---

4. Query: Find the Row with the Maximum Count

To identify the (a, b) pair that appears most frequently:

WITH RowCounts AS (
    SELECT a, b, COUNT(*) AS Count
    FROM T
    GROUP BY a, b
)
SELECT a, b, Count
FROM RowCounts
WHERE Count = (SELECT MAX(Count) FROM RowCounts);

Result: | a | b | Count | |—-|—-|——-| | 10 | 20 | 3 |

---

5. Query: Filter Rows Based on Conditions

For example, selecting rows where a > 10 and b < 30:

WITH FilteredRows AS (
    SELECT *
    FROM T
    WHERE a > 10 AND b < 30
)
SELECT *
FROM FilteredRows;

Result: | a | b | |—-|—-| | 11 | 20 |

---

6. Query: Summing Values in a CTE

You may want to calculate the sum of a and b for the entire table:

WITH TotalSums AS (
    SELECT SUM(a) AS SumA, SUM(b) AS SumB
    FROM T
)
SELECT SumA, SumB
FROM TotalSums;

Result: | SumA | SumB | |——|——| | 129 | 157 |

---

7. Query: Identify Unique Rows Only

If you want rows that appear exactly once in the table:

WITH RowCounts AS (
    SELECT a, b, COUNT(*) AS Count
    FROM T
    GROUP BY a, b
)
SELECT a, b
FROM RowCounts
WHERE Count = 1;

Result: | a | b | |—-|—-| | 11 | 20 | | 88 | 77 |

---

8. Query: Recursive CTE Example

If you want to generate a sequence starting from the smallest value in column a (e.g., 10) and increment it up to 15:

WITH RECURSIVE Sequence AS (
    SELECT MIN(a) AS n
    FROM T
    UNION ALL
    SELECT n + 1
    FROM Sequence
    WHERE n < 15
)
SELECT n
FROM Sequence;

Result: | n | |—-| | 10 | | 11 | | 12 | | 13 | | 14 | | 15 |

---

These examples showcase how CTEs can simplify and modularize SQL queries in SQLite, making them easier to write and read while handling common tasks like deduplication, filtering, aggregation, and recursion.

Summary

Common Table Expressions (CTE) in SQL provides a temporary, named result set defined within the scope of a single query using the WITH keyword. It simplifies complex queries, improves readability, and enables recursion. CTEs are particularly useful for tasks like breaking down intricate logic into modular steps, removing duplicate rows, counting occurrences of specific data, filtering, aggregating, and handling recursive operations such as hierarchical data traversal or generating sequences.

While not mandatory, CTEs make queries more maintainable and expressive, especially for intermediate calculations or when logic needs to be reused within a single SQL statement.

---

Files & Resources

All Files for Lesson 70.114

---

References

No references.

Errata

Let us know.