PL/SQL Common Table Expressions

PL/SQL Common Table Expressions (CTEs) make complex queries easier to write and understand by creating temporary result sets. We can use it multiple times in the same query. They help organize and simplify your code, especially for handling hierarchical data or recursive relationships. CTEs make your queries more efficient compared to subqueries or temporary tables.

PL/SQL Common Table Expressions

In PL/SQL, Common Table Expressions (CTEs) are a powerful SQL feature that enhances query structure by allowing you to define temporary result sets. CTEs can be referenced within the same query. These result sets exist only for the duration of the query execution. It offers a cleaner and more readable way to handle complex queries.

A CTE is essentially a named subquery referenced multiple times within the main SQL statement. This improves code readability, maintainability, and query performance optimization. Developers can use CTEs to break down large and intricate queries into simpler components, making them easier to understand and maintain.

Syntax:

WITH cte_name AS (
    -- Your CTE query goes here
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT * 
FROM cte_name;

Key terms:

• WITH clause: This keyword makes the start of the CTE. It also enables us to define one or more CTEs that we can refer to within the main query.
  
• cte_name: This is the name given to the CTE. We can refer to this name in the main query as if it were a table.
  
• AS: This keyword is used to define the query that produces the result set for the CTE.
  
• CTE Query: The query inside the CTE may be a normal SQL query. It can select data from tables, perform joins, apply filters, etc.
  
• Main Query: You can refer to it in the main query after defining the CTE. This main query can select data from the result set produced by CTE, or this result can also be joined to other tables, or can be used in any other SQL operation.

Benefits of Using CTEs

1. Readability Improved

One of the greatest strengths of working with CTEs is that they greatly clean up SQL code. Long and intricate queries are more easily simplified into smaller, named pieces. This allows CTEs to understand the logic in a query, which in turn simplifies how a query will be interpreted and kept up.

Example : Instead of deeply nested subqueries, CTEs enable you to clearly define intermediate results with meaningful names.

Query:

WITH Sales_CTE AS (
    SELECT product_id, SUM(quantity_sold) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT * FROM Sales_CTE WHERE total_sales > 1000;

2. Reusability

CTEs can be referenced more than once in the same query, allowing us to reuse the very same result set without repeating complex logic. This keeps your query leaner and avoids redundant query logic.

Example: If you have to calculate totals and then filter them a number of times, you can refer back to the CTE in each successive part of the query.

Query:

WITH Sales_CTE AS (
    SELECT product_id, SUM(quantity_sold) AS total_sales
    FROM sales
    GROUP BY product_id
)
SELECT product_id, total_sales
FROM Sales_CTE
WHERE total_sales > 1000;

-- Reusing the same CTE in another context
SELECT product_id
FROM Sales_CTE
WHERE total_sales < 500;

3. Modularity

CTEs encourage modular code. We can build queries in a step-like fashion by breaking down the query into its logical components via separate CTEs. Each part of the query can be isolated and tested, which makes the overall query easier to maintain and debug.

Example You can define different CTEs for different parts of the data transformation process and then combine them on the main query.

Query:

WITH EmployeeDetails AS (
    SELECT employee_id, first_name, last_name, department_id
    FROM employees
), DepartmentDetails AS (
    SELECT department_id, department_name
    FROM departments
)
SELECT e.first_name, e.last_name, d.department_name
FROM EmployeeDetails e
JOIN DepartmentDetails d ON e.department_id = d.department_id;

4. Recursive Query Support

CTEs also provide support for recursive queries, which are essential in querying hierarchical or tree-like data structures, such as organizational charts or bill-of-materials data. Recursive CTEs are cleaner and more efficient compared to traditional methods that used self-joins or CONNECT BY clauses in traversing hierarchies.

Example: A recursive CTE can be utilized in finding all subordinates of a manager in an employee hierarchy.

Query:

WITH RECURSIVE EmployeeHierarchy AS (
    -- Base case (top-level employees)
    SELECT employee_id, manager_id, first_name, last_name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive step (employees reporting to those in the base case)
    SELECT e.employee_id, e.manager_id, e.first_name, e.last_name
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;

Recursive CTEs in PL/SQL

One of the powerful features of SQL is recursive CTEs, which enable the developer to handle queries with recursion. This kind of queries will be very useful when dealing with hierarchical data structures such as organizational charts, bill of materials, family trees, or any dataset that has a parent-child relationship.

In a recursive CTE, a CTE refers to itself and, therefore, is able to iterate over data to build a result set step by step. A recursive CTE consists of two elements:

• Anchor Member - anchor query that supplies the initial result set.
  
• Recursive Member - This element references the CTE itself, making use of it to get the next level in recursion.

The two are combined into one by making use of a UNION ALL operator, and then the recursion could keep going until no more rows satisfy the conditions within the recursive member.

Syntax:

WITH RECURSIVE cte_name AS (
    -- Anchor member (base case)
    SELECT column1, column2, ...
    FROM table
    WHERE condition

    UNION ALL

    -- Recursive member (recursive case)
    SELECT column1, column2, ...
    FROM table
    JOIN cte_name ON join_condition
    WHERE condition
)
SELECT * FROM cte_name;

Key Terms:

• WITH RECURSIVE: This keyword defines the CTE and indicates that it is recursive.
  
• Anchor Member: The non-recursive part, which forms the base case.
  
• Recursive Member: The part that calls the CTE and performs the recursion.

Example of Recursive CTE in Action

Let’s consider a classic use case—an employee hierarchy. In this scenario, we have a table of employees where each employee has a manager, and the goal is to retrieve all employees under a given manager, including their subordinates, in a hierarchical fashion.

1. Employee Table

Query:

CREATE TABLE employees (
employee_id INT PRIMARY KEY,     
manager_id INT,     
first_name VARCHAR(50),     
last_name VARCHAR(50),     
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
 ); 

INSERT INTO employees (employee_id, manager_id, first_name, last_name) 
VALUES (1, NULL, 'John', 'Doe'),        -- Top-level manager (no manager_id)
 (2, 1, 'Jane', 'Smith'),         -- Reports to John 
(3, 1, 'Alex', 'Brown'),         -- Reports to John 
(4, 2, 'Emily', 'Johnson'),      -- Reports to Jane 
(5, 2, 'Michael', 'White'),      -- Reports to Jane 
(6, 3, 'Sarah', 'Miller'),       -- Reports to Alex 
(7, 4, 'Emma', 'Davis');         -- Reports to Emily

Output:

Explanation:

• employee_id: Unique identifier for each employee.
  
• manager_id: Points to the employee_id of the employee's manager. A NULL value means the employee has no manager (i.e., a top-level manager).

Find Employee Hierarchy

This query uses a recursive Common Table Expression (CTE) to find the hierarchy of employees in an organization.

1. The anchor member selects top-level managers (where manager_id is NULL), establishing the starting point of the hierarchy.
   
2. The recursive member retrieves employees reporting to the current employee by joining the employees table with the CTE itself, creating a hierarchy chain.
   
3. The final SELECT statement retrieves all employee details from the recursive CTE, effectively building and displaying the entire employee hierarchy.

Query:

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: Select the top-level manager (manager_id IS NULL)
    SELECT employee_id, first_name, last_name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member: Find employees reporting to the current employee
    SELECT e.employee_id, e.first_name, e.last_name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, first_name, last_name, manager_id
FROM EmployeeHierarchy;

Output:

Explanation:

• Anchor Member: The anchor query selects the top-level employees, those do not have managers.
  
• Recursive member: This returns all the direct subordinates of every employee in the current level by joining the Employees table with the recursive CTE, EmployeeHierarchy.
  
• Result: This query builds the full hierarchy of employees and their relationships based on the manager_id.

2. Family Table

This query creates a family table where each person has a person_id and an optional parent_id, creating a hierarchical relationship to track family ancestry.

The foreign key constraint ensures that the parent_id references a valid person_id in the same table, allowing for a self-referential structure. The INSERT statements populate the table with a family tree, starting with John Adams as the root ancestor.

Query:

CREATE TABLE family (
    person_id INT PRIMARY KEY,
    parent_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    FOREIGN KEY (parent_id) REFERENCES family(person_id)
);

INSERT INTO family (person_id, parent_id, first_name, last_name) VALUES
(1, NULL, 'John', 'Adams'),      -- Root ancestor (no parent)
(2, 1, 'Robert', 'Adams'),       -- Child of John
(3, 1, 'Alice', 'Adams'),        -- Child of John
(4, 2, 'Brian', 'Adams'),        -- Child of Robert
(5, 2, 'Jenny', 'Adams'),        -- Child of Robert
(6, 4, 'David', 'Adams');        -- Child of Brian

Output:

Explanation:

• The output displays a family tree with each person's ID, parent ID, first name, and last name.
  
• John Adams is the root ancestor with no parent (parent_id = NULL).
  
• while his descendants (Robert, Alice, Brian, Jenny, and David) are linked to him and each other through the parent_id field, showing the generational relationships within the family.

Recursive CTE Query to Find All Ancestors of David Adams (person_id = 6)

This recursive CTE query identifies all ancestors of David Adams by starting with his record (person_id = 6) as the anchor. It recursively joins the family table with the CTE to trace back through his parents and ancestors. The result is a list of all individuals in David Adams' ancestry.

Query:

WITH RECURSIVE AncestorCTE AS (
    -- Anchor member: Start with the given person (David Adams)
    SELECT person_id, parent_id, first_name, last_name
    FROM family
    WHERE person_id = 6

    UNION ALL

    -- Recursive member: Find the person's parents and ancestors
    SELECT f.person_id, f.parent_id, f.first_name, f.last_name
    FROM family f
    INNER JOIN AncestorCTE a ON f.person_id = a.parent_id
)
SELECT * FROM AncestorCTE;

Output

Explanation:

• The output shows the ancestry of David Adams, starting with him (person_id = 6) and tracing back through his ancestors.
  
• The query first retrieves David, then recursively finds his father (Brian), grandfather (Robert), and great-grandfather (John), displaying each ancestor's person_id, parent_id, first name, and last name in the hierarchy.
  
• This reveals David's full lineage back to the root ancestor, John Adams.

Conclusion

PL/SQL Common Table Expressions provide a nice tool for simplifying complicated queries by providing clear code. They do provide modular query design by naming temporary result sets for future reuse in queries, especially recursion and hierarchical data processing.

This makes CTEs a more readable, maintainable, and performant code than traditional subqueries and temporary tables while developing in PL/SQL. Thus, with CTEs, developers are able to craft more structured and efficient SQL queries.