Sql Common Table Expressions And Recursive Queries Complete Guide

Table of Contents

1. Introduction to Common Table Expressions (CTEs)
2. Basic Syntax of CTEs
3. Using CTEs in a Simple Query
4. Introduction to Recursive Queries
5. Basic Syntax of Recursive Queries
6. Example of a Recursive Query: Organizational Hierarchy
7. Understanding Anchor and Recursive Members
8. Practical Uses of CTEs and Recursive Queries

1. Introduction to Common Table Expressions (CTEs)

Common Table Expressions (CTEs) are like temporary result sets that you can refer to within your SQL query. They are defined using the WITH clause and are useful for writing complex queries in a simpler and more readable way. CTEs can be used in place of derived tables or subqueries.

2. Basic Syntax of CTEs

The general syntax of a CTE is as follows:

WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

• WITH: Indicates the start of a CTE.
• cte_name: The name you give to the CTE, which you will use in your query.
• AS: Follows after the CTE name and is followed by the definition, which is enclosed in parentheses.
• SELECT statement inside the CTE: Defines the data within the CTE.

Example:

Let’s assume we have a table called products with columns id, name, price, and category. We want to create a CTE named expensive_products that lists all products priced over $1000.

WITH expensive_products AS (
    SELECT id, name, price
    FROM products
    WHERE price > 1000
)
SELECT id, name, price 
FROM expensive_products;

3. Using CTEs in a Simple Query

Let's continue with our products example. Suppose there is another table called sales with columns product_id, quantity_sold, sale_date, and store_id. Now, we want to find out how much revenue was generated by each product in the expensive_products CTE.

WITH expensive_products AS (
    SELECT id, name, price
    FROM products
    WHERE price > 1000
),
product_sales AS (
    SELECT product_id, SUM(quantity_sold) AS total_quantity_sold,
           SUM(quantity_sold * price) AS total_revenue
    FROM sales s
    JOIN expensive_products ep ON s.product_id = ep.id
    GROUP BY product_id
)
SELECT ps.product_id, ep.name, ps.total_quantity_sold, ps.total_revenue
FROM product_sales ps
JOIN expensive_products ep ON ps.product_id = ep.id
ORDER BY ps.total_revenue DESC;

In this example:

• expensive_products defines the subset of products to consider (those priced over $1000).
• product_sales computes the total quantity sold and revenue generated by each product in expensive_products.
• Finally, the outermost SELECT fetches the product details and computed sales metrics.

4. Introduction to Recursive Queries

Recursive queries are a type of CTE where the result set includes a reference to itself. This is useful for tasks such as generating hierarchical data structures, working with self-referential tables (like organizational hierarchies), and unfolding sequences.

5. Basic Syntax of Recursive Queries

The syntax of a recursive CTE includes a union between an initial non-recursive member and a recursive member that references the CTE itself:

WITH RECURSIVE cte_name (column_list) AS (
    -- Recursive Anchor Member (initialization)
    SELECT initial_columns
    FROM initial_table
    WHERE initial_condition
    
    UNION [ALL|DISTINCT]
    
    -- Recursive Member (self-reference)
    SELECT recursive_columns
    FROM cte_name
    JOIN other_tables ON join_conditions
    WHERE recursive_conditions
)
SELECT * FROM cte_name;

• WITH RECURSIVE: Initiates a recursive CTE.
• Anchor member: This is the initial query that returns the first row(s) of the result set.
• Recursive member: This member joins the CTE back to the main table or another query to compute additional rows based on the previously computed rows.
• UNION [ALL|DISTINCT]: Combines the results from the anchor and recursive members. Use DISTINCT if you need unique values; otherwise, use ALL, which is usually preferable for recursive queries to include all rows.

6. Example of a Recursive Query: Organizational Hierarchy

Assume we have a table called employees with columns:

• employee_id
• name
• manager_id (points to the employee_id of their manager)

We want to generate a hierarchy for employee John, listing all his subordinates recursively down to the nth level.

Sample Data

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'John', NULL),
(2, 'Alice', 1),
(3, 'Bob', 1),
(4, 'Charlie', 2),
(5, 'Diana', 3),
(6, 'Eve', 2);

Query to Generate Hierarchy

WITH RECURSIVE employee_hierarchy(employee_id, name, manager_id, depth) AS (
    -- Anchor Member
    SELECT employee_id, name, manager_id, 1 AS depth
    FROM employees
    WHERE name = 'John'
    
    UNION ALL
    
    -- Recursive Member
    SELECT e.employee_id, e.name, e.manager_id, eh.depth + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, name, manager_id, depth
FROM employee_hierarchy
ORDER BY depth, name;

Here’s a breakdown:

• Anchor Member: This starts by selecting John (since manager_id is NULL for him, indicating he is at the top level of the hierarchy).
• Recursive Member: This joins the employees table with the employee_hierarchy CTE (referencing itself) to find all employees whose manager_id matches the employee_id already found in the hierarchy. It also increments the depth by 1.
• Result: The final SELECT fetches all rows and orders them first by depth level and then by name.

Result for Given Data

| employee_id | name | manager_id | depth | |-------------|-----------|------------|-------| | 1 | John | NULL | 1 | | 2 | Alice | 1 | 2 | | 3 | Bob | 1 | 2 | | 4 | Charlie | 2 | 3 | | 5 | Diana | 3 | 3 | | 6 | Eve | 2 | 3 |

This result shows John at the top (depth 1) with Alice and Bob (depth 2) as his direct reports, and Charlie, Diana, and Eve (depth 3) as second-level reports under Alice and Bob.

7. Understanding Anchor and Recursive Members

In recursive queries, there are two main parts to the CTE:

1. Anchor Member: The non-recursive part that initializes the CTE. It typically returns the starting or base rows from the main table.
2. Recursive Member: The portion that generates new rows based on the rows already selected by the CTE. It joins the CTE to the main table (or another table/views) and adds more rows iteratively.

Steps:

1. The anchor member runs first, returning the initial rows.
2. The recursive member runs next and checks if there are any new rows to add based on the rows returned by the CTE so far.
3. Steps 1 & 2 repeat until no new rows can be added.

Preventing Infinite Loops

To prevent infinite loops, it’s essential to ensure that the WHERE condition or logic in the recursive member eventually leads to a stopping point. In most cases, the recursive member should include a termination condition based on the data available.

Common Termination Condition:

• Check that the manager_id does not match any employee_id already in the hierarchy.

8. Practical Uses of CTEs and Recursive Queries

a. Simplify Complex Queries

Using CTEs can break down complex queries into manageable parts, improving readability and maintainability.

-- Example without CTE
SELECT customer_id, customer_name, SUM(order_amount)
FROM (SELECT c.customer_id, c.customer_name, o.amount AS order_amount
      FROM customers c
      JOIN orders o ON c.customer_id = o.customer_id
      WHERE o.status = 'delivered')
GROUP BY customer_id, customer_name;

-- With CTE
WITH delivered_orders AS (
    SELECT c.customer_id, c.customer_name, o.amount AS order_amount
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.status = 'delivered'
)
SELECT customer_id, customer_name, SUM(order_amount) AS total_delivered_amount
FROM delivered_orders
GROUP BY customer_id, customer_name;

b. Hierarchical Data Structures

Recursive CTEs are perfect for dealing with hierarchical data structures such as organizational trees, category trees, nested comments, etc.

• Employee Reports
• Product Categories
• Comments on Blog Posts
• Filesystem Folders
• Family Trees

c. Generating Sequences

You can use recursive queries to generate numerical sequences, dates, or even Fibonacci numbers.

Example: Generational Sequence

Generate sequence for 1 to 10:

WITH RECURSIVE generation AS (
    SELECT 1 AS num
    
    UNION ALL
    
    SELECT num + 1
    FROM generation
    WHERE num < 10
)
SELECT num FROM generation;

This CTE starts with num = 1 and keeps adding 1 until num reaches 10.

Summary

• CTEs provide a way to write cleaner and more understandable queries. You define them using the WITH clause.
• Recursive CTEs allow you to repeatedly query a data structure based on previously computed results, making them ideal for tasks involving hierarchies or sequences.
• Recursive Queries generally consist of two main parts: the anchor member to initialize the data and the recursive member to add subsequent rows.
• Prevent Infinite Loops: Always ensure there is a termination condition in your recursive member.

These concepts are foundational yet powerful in SQL, helping you manage and manipulate complex datasets efficiently. Practice regularly with real-world examples to reinforce your understanding.

Additional Resources

• PostgreSQL Documentation - Detailed explanation with examples (applicable to MySQL 8+, SQL Server, Oracle).
• SQLzoo (CTEs Section) - Interactive exercises to practice writing CTEs.
• YouTube Tutorials - Video guides explaining concepts visually and practically.