CTE (Common Table Expressions) - Recursive and Non-recursive in SQL

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs improve the readability and organization of complex queries by breaking them into smaller, reusable components. CTEs can either be recursive or non-recursive, depending on whether they reference themselves. In this article, we will explore both types of CTEs, their syntax, and provide examples of how to use them in SQL.

What is a Common Table Expression (CTE)?

A CTE is defined using the WITH keyword followed by the CTE name and the query that produces the temporary result set. A CTE is valid only for the duration of the query in which it is used. CTEs are particularly useful when you need to perform multiple operations on the same result set or when a query needs to be broken into simpler parts.

Non-recursive CTE

A non-recursive CTE is a simple CTE that does not reference itself. It is used to store intermediate results that can be reused in the main query.

Syntax for Non-recursive CTE

        WITH cte_name AS (
            -- Query to generate the CTE result set
            SELECT column1, column2
            FROM table_name
            WHERE condition
        )
        -- Main query that uses the CTE
        SELECT * FROM cte_name;
    

Example of Non-recursive CTE

Let’s say we have a table of employees, and we want to find the employees who earn more than $50,000. We can use a non-recursive CTE to store this result and query it later.

        CREATE TABLE employees (
            employee_id INT,
            employee_name VARCHAR(100),
            salary DECIMAL(10, 2)
        );

        INSERT INTO employees (employee_id, employee_name, salary)
        VALUES (1, 'John Doe', 55000),
               (2, 'Jane Smith', 45000),
               (3, 'Tom Brown', 60000),
               (4, 'Emily White', 40000);

        -- Non-recursive CTE to find employees earning more than $50,000
        WITH high_salary_employees AS (
            SELECT employee_name, salary
            FROM employees
            WHERE salary > 50000
        )
        SELECT * FROM high_salary_employees;
    

In this example, the high_salary_employees CTE contains a list of employees earning more than $50,000, and the main query simply selects all records from the CTE.

Recursive CTE

A recursive CTE is a CTE that references itself. It is useful for querying hierarchical or tree-structured data, such as organizational charts, file systems, or bill-of-materials data.

Syntax for Recursive CTE

        WITH RECURSIVE cte_name AS (
            -- Base case (non-recursive part)
            SELECT column1, column2
            FROM table_name
            WHERE condition
            UNION ALL
            -- Recursive case
            SELECT t.column1, t.column2
            FROM table_name t
            JOIN cte_name cte ON t.column = cte.column
        )
        -- Main query that uses the recursive CTE
        SELECT * FROM cte_name;
    

Example of Recursive CTE

Consider a table that stores employee-manager relationships in an organization. We want to find all employees in the organization starting from a specific manager. This is a classic example of a hierarchical relationship that can be handled by a recursive CTE.

        CREATE TABLE employees (
            employee_id INT,
            employee_name VARCHAR(100),
            manager_id INT
        );

        INSERT INTO employees (employee_id, employee_name, manager_id)
        VALUES (1, 'John Doe', NULL),
               (2, 'Jane Smith', 1),
               (3, 'Tom Brown', 1),
               (4, 'Emily White', 2),
               (5, 'David Green', 2);

        -- Recursive CTE to find all employees under manager 'John Doe' (employee_id = 1)
        WITH RECURSIVE employee_hierarchy AS (
            -- Base case: Select the manager (John Doe)
            SELECT employee_id, employee_name, manager_id
            FROM employees
            WHERE employee_id = 1
            UNION ALL
            -- Recursive case: Select employees under the manager
            SELECT e.employee_id, e.employee_name, e.manager_id
            FROM employees e
            JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
        )
        SELECT * FROM employee_hierarchy;
    

In this example, the employee_hierarchy CTE first selects the manager 'John Doe' (the base case), then recursively selects employees managed by John Doe and so on. The final result will include John Doe, his direct reports (Jane Smith and Tom Brown), and their reports (Emily White and David Green).

Key Points to Remember

• Non-recursive CTEs: Are used for simple queries that do not require recursion. They allow you to break down complex queries into manageable parts.
• Recursive CTEs: Are useful for hierarchical or recursive data. They involve two parts: the base case and the recursive case. The base case initializes the recursion, while the recursive case continues to reference the CTE until no more records are found.
• UNION ALL: In a recursive CTE, the UNION ALL operator is used to combine the base and recursive results. It ensures that all rows from both parts are included, even if they have duplicate values.
• Performance Considerations: Recursive CTEs can be expensive in terms of performance, especially when the recursion depth is large. Always ensure that recursion will eventually terminate to avoid infinite loops.

Conclusion

Common Table Expressions (CTEs) are powerful tools in SQL that help organize complex queries into more readable and maintainable components. Whether you are using a non-recursive CTE for simple queries or a recursive CTE for hierarchical data, CTEs improve query structure and reduce repetition. By understanding the differences between recursive and non-recursive CTEs, you can effectively use them to handle a wide range of database tasks.