Modern data analysis often requires complex SQL queries, which can quickly become challenging to understand and maintain. Common Table Expressions (CTEs) offer an elegant solution to simplify and organize these queries, making them more readable and modular. In this blog, we will explore CTEs in-depth, focusing on their benefits, practical use-cases, and how they can significantly improve your SQL querying experience.

What are Common Table Expressions (CTEs)?

Common Table Expressions (CTEs) are temporary result sets in SQL, which can be referenced within the execution of a SELECT, INSERT, UPDATE, or DELETE statement. They allow SQL developers to break complex queries into manageable, reusable blocks, which helps to simplify logic and improve code readability.

CTEs can be particularly useful when your query has multiple layers of subqueries or requires repeated reference to a complex computation. Unlike subqueries, CTEs can be defined once and used multiple times, making them extremely versatile and efficient.

Syntax of a Common Table Expression

The syntax of a CTE is quite straightforward, and it starts with the WITH keyword:

WITH cte_name AS (
    SELECT column1, column2
    FROM some_table
    WHERE some_condition
)
SELECT *
FROM cte_name;

In this example, cte_name represents the Common Table Expression. The WITH clause defines a temporary result set that can then be used in the main query. This modular structure makes it easy to troubleshoot, debug, and read your queries.

Benefits of Using CTEs

1. Improved Readability

The main advantage of CTEs is improved readability. Complex SQL queries involving multiple nested subqueries can be extremely difficult to understand and maintain. By using CTEs, developers can logically segment the problem, giving each segment an intuitive name and purpose.

For example:

WITH customer_total_orders AS (
    SELECT customer_id, COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id
)
SELECT *
FROM customer_total_orders
WHERE total_orders > 10;

In the above code, the CTE customer_total_orders allows you to logically represent the intermediate calculation of the total orders per customer, making the entire query much easier to comprehend.

2. Modularity and Reusability

Another important benefit of CTEs is modularity. You can write modular code, where each part has a clear responsibility. In scenarios where the same subquery is referenced multiple times, using a CTE makes your code more concise.

Consider this example:

WITH high_sales_products AS (
    SELECT product_id, product_name, sales_amount
    FROM products
    WHERE sales_amount > 50000
)
, discounted_products AS (
    SELECT product_id, discount_rate
    FROM discounts
    WHERE discount_rate > 10
)
SELECT high_sales_products.product_id, high_sales_products.product_name, discounted_products.discount_rate
FROM high_sales_products
JOIN discounted_products ON high_sales_products.product_id = discounted_products.product_id;

In this case, high_sales_products and discounted_products are two different CTEs that are then joined in the final SELECT statement. The modular structure makes it easier to understand each segment of the query independently.

Recursive CTEs

In addition to non-recursive CTEs, SQL also allows recursive CTEs. A recursive CTE refers back to itself and is typically used to work with hierarchical data structures such as organization charts or file directories.

Syntax of a Recursive CTE

Recursive CTEs consist of two parts: an anchor member and a recursive member, separated by a UNION ALL.

WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT *
FROM org_chart;

In this example, the anchor member retrieves the top-level employee records, while the recursive member iteratively finds the subordinates of each employee until all records are returned.

Use Cases of CTEs

1. Simplifying Hierarchical Queries

One of the primary use cases for recursive CTEs is to manage hierarchical data. For instance, in an employee-manager relationship, CTEs allow easy traversal of the hierarchy to determine all subordinates of a particular employee.

Consider the following query that prints the hierarchy of employees in a company:

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy
ORDER BY level;

In this query, we define a CTE called employee_hierarchy to recursively build the list of employees and their corresponding levels in the organizational structure.

2. Breaking Down Complex Aggregations

CTEs are also useful when breaking down complex aggregations into smaller parts. This helps avoid redundant aggregations and makes the query much more readable.

For example, calculating the revenue share of products by category could be easily achieved using CTEs:

WITH category_revenue AS (
    SELECT category_id, SUM(price * quantity) AS total_revenue
    FROM sales
    GROUP BY category_id
)
, product_revenue AS (
    SELECT product_id, category_id, SUM(price * quantity) AS product_total
    FROM sales
    GROUP BY product_id, category_id
)
SELECT p.product_id, p.product_total, c.total_revenue, (p.product_total / c.total_revenue) * 100 AS revenue_share
FROM product_revenue p
JOIN category_revenue c ON p.category_id = c.category_id;

In the above query, the use of two CTEs (category_revenue and product_revenue) makes the calculation of revenue share intuitive, while maintaining clarity in the logic.

CTEs vs. Subqueries

Feature	CTEs	Subqueries
Readability	More readable, easier to segment	Hard to read when deeply nested
Reusability	Reusable within the same query	Not reusable, needs duplication
Performance	Better for simplifying complex logic	May have similar or worse performance in certain scenarios

While subqueries can achieve similar functionality, they become cumbersome when used multiple times within a single query. CTEs offer a better structure for reusability and maintainability.

Limitations of CTEs

While CTEs have numerous advantages, there are a few limitations to be aware of:

1. Scope Limitations

CTEs are only available within the query in which they are defined. They cannot be reused across different queries unless redefined each time. This makes them unsuitable for scenarios requiring broader query reuse.

2. Performance Considerations

CTEs are primarily for enhancing readability. In some cases, especially when nested CTEs are used, performance can degrade compared to direct use of subqueries or temporary tables. It is essential to test the performance of the queries in your specific use case.

Practical Example: CTE for Monthly Sales Analysis

Consider a real-world example where we need to analyze the monthly sales data and compare it with the previous month:

WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS sales_month, SUM(total_amount) AS total_sales
    FROM sales
    GROUP BY sales_month
),
previous_month_sales AS (
    SELECT sales_month, LAG(total_sales, 1) OVER (ORDER BY sales_month) AS prev_sales
    FROM monthly_sales
)
SELECT m.sales_month, m.total_sales, p.prev_sales,
       CASE
           WHEN p.prev_sales IS NULL THEN 0
           ELSE ((m.total_sales - p.prev_sales) / p.prev_sales) * 100
       END AS sales_growth
FROM monthly_sales m
LEFT JOIN previous_month_sales p ON m.sales_month = p.sales_month;

In this example, we used two CTEs (monthly_sales and previous_month_sales). The monthly_sales CTE calculates the sales for each month, and previous_month_sales calculates the value of sales in the previous month to help compute the growth. Using CTEs in this case keeps each part of the analysis distinct and readable.

Conclusion

Common Table Expressions (CTEs) are a powerful tool in SQL, providing a way to simplify complex queries, enhance readability, and improve the maintainability of code. They serve as an ideal approach to handle modularity, particularly for hierarchical data or when dealing with complex multi-step transformations. CTEs can also make SQL scripts much more understandable for both seasoned developers and newcomers alike.

Their ability to enhance query readability, along with simplifying code maintenance, makes them a preferred choice for breaking down challenging SQL logic into manageable components. However, it is essential to consider their limitations, especially regarding performance and scope.

If you haven’t explored CTEs in-depth yet, now is the perfect time to start experimenting with them in your SQL projects.

Disclaimer: This blog is intended for educational purposes and reflects best practices as of the time of writing. SQL behaviors may vary based on the RDBMS used. Please report any inaccuracies, so we can correct them promptly.