Common Table Expressions (CTEs), introduced in Oracle with the WITH clause, are temporary, named result sets that you can reference within a single SQL statement (e.g., SELECT, INSERT, UPDATE, DELETE). They enhance query readability, promote code reusability, and can simplify complex analytical queries, often leading to better performance optimization. CTEs are especially useful for breaking down complex problems into smaller, more manageable logical steps.

Benefits: Readability, Reusability, Performance Optimization

CTEs improve readability by allowing you to define subqueries separately with meaningful names. This makes complex SQL easier to understand and debug. They promote reusability as a defined CTE can be referenced multiple times within the same main query, avoiding redundant code. While not a guaranteed performance boost, CTEs can sometimes optimize performance for complex queries by allowing the optimizer to process intermediate results more efficiently, especially when dealing with hierarchical or recursive data.

Example 1: Basic CTE for Improved Readability

-- Define a CTE named "high_salary_employees" to find employees earning more than 7000
WITH high_salary_employees AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        salary
    FROM
        employees
    WHERE
        salary > 7000
)
-- Select all columns from the CTE
SELECT
    employee_id,
    first_name,
    last_name,
    salary
FROM
    high_salary_employees
WHERE
    last_name LIKE 'S%'; -- Further filter the results from the CTE

Explanation This example demonstrates a basic CTE named high_salary_employees that selects employees with a salary greater than 7000. The main SELECT statement then queries this CTE, showcasing how CTEs make the query flow clearer and more modular.

Example 2: Multiple CTEs in a Single Query

-- CTE to get average salary per department
WITH dept_avg_salary AS (
    SELECT
        department_id,
        AVG(salary) AS avg_dept_sal
    FROM
        employees
    GROUP BY
        department_id
),
-- CTE to get employees with salary above their department average
employees_above_avg AS (
    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.salary,
        e.department_id,
        das.avg_dept_sal
    FROM
        employees e
    JOIN
        dept_avg_salary das ON e.department_id = das.department_id
    WHERE
        e.salary > das.avg_dept_sal
)
-- Final selection from the second CTE
SELECT
    employee_id,
    first_name,
    last_name,
    salary,
    department_id,
    avg_dept_sal
FROM
    employees_above_avg
ORDER BY
    department_id, salary DESC;

Explanation Here, two CTEs are defined sequentially. dept_avg_salary calculates the average salary for each department, and employees_above_avg then uses this result to find employees earning more than their department's average. This illustrates how CTEs can build upon each other for complex logic.

Example 3: CTE for Data Insertion (DML with CTE)

-- CTE to select data to be inserted
WITH new_department_employees AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        100 AS salary, -- Assign a default salary for new hires
        NULL AS commission_pct,
        270 AS manager_id, -- A new manager ID
        80 AS department_id -- Assign to a specific new department
    FROM
        employees
    WHERE
        ROWNUM <= 3 -- Selecting first 3 employees as an example
)
-- Insert data from the CTE into the employees table
INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
)
SELECT
    (SELECT MAX(employee_id) FROM employees) + ROWNUM, -- Generate unique employee_id
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    commission_pct,
    manager_id,
    department_id
FROM
    new_department_employees;

-- COMMIT; -- Uncomment to make the changes permanent

Explanation This example demonstrates using a CTE within an INSERT statement. The new_department_employees CTE defines the data to be inserted, including a generated employee_id. This approach enhances the readability and organization of DML operations involving derived data.

Example 4: CTE for Data Update (DML with CTE)

-- CTE to identify employees whose salary needs to be updated
WITH employees_to_update AS (
    SELECT
        employee_id,
        salary
    FROM
        employees
    WHERE
        department_id = 50 AND salary < 5000
)
-- Update the salary for identified employees using the CTE
UPDATE employees e
SET
    salary = e.salary * 1.10 -- Increase salary by 10%
WHERE
    e.employee_id IN (SELECT employee_id FROM employees_to_update);

-- ROLLBACK; -- Uncomment to revert changes for testing

Explanation This example showcases a CTE used in an UPDATE statement. The employees_to_update CTE identifies specific employees based on department and current salary, making the UPDATE logic clearer and more manageable.

Example 5: CTE for Data Deletion (DML with CTE)

-- CTE to identify employees to be deleted
WITH employees_to_delete AS (
    SELECT
        employee_id
    FROM
        employees
    WHERE
        department_id = 40
        AND hire_date < TO_DATE('2000-01-01', 'YYYY-MM-DD')
)
-- Delete employees based on the CTE results
DELETE FROM employees
WHERE employee_id IN (SELECT employee_id FROM employees_to_delete);

-- ROLLBACK; -- Uncomment to revert changes for testing

Explanation In this example, a CTE named employees_to_delete selects employees from a specific department who were hired before a certain date. The subsequent DELETE statement then uses this CTE to remove those identified records, making the deletion criteria explicit and easy to follow.

Recursive CTEs (Hierarchical Queries)

Recursive CTEs are a powerful feature of the WITH clause that allow you to traverse hierarchical data, such as organizational charts, bill of materials, or network paths. They consist of an anchor member (the starting point) and a recursive member (which references the CTE itself to build the hierarchy). Oracle 19c's WITH RECURSIVE syntax or the standard CONNECT BY clause can achieve hierarchical queries, but Recursive CTEs often offer more flexibility and standard SQL compliance.

Example 1: Simple Employee Hierarchy (Manager-Employee)

-- Recursive CTE to find all employees under a specific manager
WITH employee_hierarchy (employee_id, first_name, last_name, manager_id, level) AS (
    -- Anchor member: Start with the top-level manager (e.g., manager_id IS NULL)
    SELECT
        employee_id,
        first_name,
        last_name,
        manager_id,
        1 AS level
    FROM
        employees
    WHERE
        manager_id IS NULL -- Starting from the CEO
    UNION ALL
    -- Recursive member: Join with the CTE to find direct reports
    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.manager_id,
        eh.level + 1
    FROM
        employees e
    JOIN
        employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Select all employees from the hierarchy
SELECT
    LPAD(' ', 2 * (level - 1)) || first_name || ' ' || last_name AS employee_name,
    level
FROM
    employee_hierarchy
ORDER BY
    level, employee_id;

Explanation This recursive CTE, employee_hierarchy, builds an organizational tree starting from the top-level manager. The anchor member identifies the root, and the recursive member iteratively finds direct reports, incrementing the level to represent the depth in the hierarchy.

Example 2: Bill of Materials (BOM) Explosion

-- Assuming a table 'parts' with columns: part_id, part_name, parent_part_id, quantity
-- For demonstration, let's create a temporary table
CREATE GLOBAL TEMPORARY TABLE parts (
    part_id VARCHAR2(10),
    part_name VARCHAR2(50),
    parent_part_id VARCHAR2(10),
    quantity NUMBER
) ON COMMIT PRESERVE ROWS;

INSERT INTO parts VALUES ('A', 'Product A', NULL, 1);
INSERT INTO parts VALUES ('B', 'Subcomponent B', 'A', 2);
INSERT INTO parts VALUES ('C', 'Part C', 'B', 3);
INSERT INTO parts VALUES ('D', 'Subcomponent D', 'A', 1);
INSERT INTO parts VALUES ('E', 'Part E', 'D', 5);

WITH bom_explosion (
    part_id,
    part_name,
    parent_part_id,
    level_num,
    total_quantity
) AS (
    -- Anchor member: Top-level products (no parent)
    SELECT
        part_id,
        part_name,
        parent_part_id,
        1 AS level_num,
        quantity AS total_quantity
    FROM
        parts
    WHERE
        parent_part_id IS NULL
    UNION ALL
    -- Recursive member: Find sub-components
    SELECT
        p.part_id,
        p.part_name,
        p.parent_part_id,
        be.level_num + 1,
        p.quantity * be.total_quantity -- Calculate cumulative quantity
    FROM
        parts p
    JOIN
        bom_explosion be ON p.parent_part_id = be.part_id
)
SELECT
    LPAD(' ', 2 * (level_num - 1)) || part_name AS bom_item,
    total_quantity,
    level_num
FROM
    bom_explosion
ORDER BY
    part_id;

DROP TABLE parts;

Explanation This example demonstrates a recursive CTE for a Bill of Materials (BOM) explosion. It starts with top-level products and then recursively finds all sub-components, calculating the cumulative quantity needed for each part. This is a common use case for recursive queries in manufacturing.

Example 3: Pathfinding in a Graph

-- Assuming a table 'routes' with columns: start_node, end_node, distance
-- For demonstration, let's create a temporary table
CREATE GLOBAL TEMPORARY TABLE routes (
    start_node VARCHAR2(10),
    end_node VARCHAR2(10),
    distance NUMBER
) ON COMMIT PRESERVE ROWS;

INSERT INTO routes VALUES ('A', 'B', 10);
INSERT INTO routes VALUES ('B', 'C', 15);
INSERT INTO routes VALUES ('A', 'D', 20);
INSERT INTO routes VALUES ('D', 'C', 5);
INSERT INTO routes VALUES ('C', 'E', 8);

WITH paths (current_node, end_node, total_distance, path, level_num) AS (
    -- Anchor member: Start from a specific node
    SELECT
        start_node,
        end_node,
        distance,
        start_node || ' -> ' || end_node,
        1 AS level_num
    FROM
        routes
    WHERE
        start_node = 'A' -- Starting point for the path
    UNION ALL
    -- Recursive member: Extend the path to connected nodes
    SELECT
        r.start_node,
        r.end_node,
        p.total_distance + r.distance,
        p.path || ' -> ' || r.end_node,
        p.level_num + 1
    FROM
        routes r
    JOIN
        paths p ON r.start_node = p.end_node
    WHERE
        INSTR(p.path, r.end_node) = 0 -- Avoid cycles
)
SELECT
    path,
    total_distance
FROM
    paths
WHERE
    end_node = 'E' -- Find paths to a specific destination
ORDER BY
    total_distance;

DROP TABLE routes;

Explanation This recursive CTE finds all possible paths from a starting node to an ending node in a simplified graph representation. The path column tracks the route taken, and the INSTR function in the WHERE clause helps to prevent infinite loops by avoiding cycles.

Example 4: Generating a Series of Dates

-- Recursive CTE to generate a series of dates for a month
WITH date_series (single_date) AS (
    -- Anchor member: Start date
    SELECT
        TO_DATE('2025-01-01', 'YYYY-MM-DD') AS single_date
    FROM
        DUAL
    UNION ALL
    -- Recursive member: Add one day until month end
    SELECT
        single_date + 1
    FROM
        date_series
    WHERE
        single_date + 1 <= TO_DATE('2025-01-31', 'YYYY-MM-DD')
)
SELECT
    single_date
FROM
    date_series
ORDER BY
    single_date;

Explanation This example demonstrates a recursive CTE to generate a series of dates. The anchor member sets the starting date, and the recursive member adds one day iteratively until the end of the month is reached, providing a clean way to generate sequential data.

Example 5: Calculating Running Totals (Non-Recursive but Common Use Case)

-- Although typically done with analytic functions, a CTE can structure this.
-- This is a non-recursive CTE, but it showcases how CTEs can stage data for further processing.
WITH sales_by_date AS (
    SELECT
        TRUNC(order_date) AS sale_date,
        SUM(quantity * unit_price) AS daily_sales
    FROM
        order_items oi
    JOIN
        orders o ON oi.order_id = o.order_id
    GROUP BY
        TRUNC(order_date)
)
SELECT
    sale_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY sale_date) AS running_total_sales
FROM
    sales_by_date
ORDER BY
    sale_date;

Explanation While not a recursive CTE, this example illustrates how a CTE can prepare an intermediate result set (sales_by_date) to which an analytic function (like SUM() OVER () for running totals) can then be applied. This pattern of "staging" data with CTEs is very common in complex reporting and analysis.