Oracle AI Database 26ai allows a WITH clause inside another WITH clause. Helper CTEs can now stay close to the query that uses them instead of being moved to the top level.

Why use nested WITH?

• to keep helper queries local to one transformation stage,
• to simplify generated SQL and migrations from other databases,
• to correlate an inner CTE with an outer query block.

Earlier Oracle releases required flattening the CTEs or using inline views.

Basic syntax

WITH outer_cte AS
(
  WITH inner_cte AS
  (
    SELECT ...
  )
  SELECT ...
  FROM inner_cte
)
SELECT ...
FROM outer_cte;

Test environment

The following examples were verified using SQL*Plus on:

Oracle AI Database 26ai Free Release 23.26.3.0.0
Version 23.26.3.0.0

Prepare test tables

Create two small tables for departments and employees:

CREATE TABLE nested_with_departments
(
  department_id   NUMBER PRIMARY KEY,
  department_name VARCHAR2(30) NOT NULL
);

CREATE TABLE nested_with_employees
(
  employee_id   NUMBER PRIMARY KEY,
  employee_name VARCHAR2(30) NOT NULL,
  department_id NUMBER NOT NULL
    REFERENCES nested_with_departments,
  salary        NUMBER(10,2) NOT NULL,
  hire_date     DATE NOT NULL
);

Insert sample data:

INSERT INTO nested_with_departments
VALUES (10, 'Engineering');

INSERT INTO nested_with_departments
VALUES (20, 'Sales');

INSERT INTO nested_with_departments
VALUES (30, 'Support');

INSERT INTO nested_with_employees
VALUES (1, 'Anna', 10, 5000, DATE '2022-01-10');

INSERT INTO nested_with_employees
VALUES (2, 'Bob', 10, 7000, DATE '2021-05-15');

INSERT INTO nested_with_employees
VALUES (3, 'Chris', 10, 9000, DATE '2020-03-20');

INSERT INTO nested_with_employees
VALUES (4, 'Diana', 20, 4000, DATE '2023-02-01');

INSERT INTO nested_with_employees
VALUES (5, 'Eric', 20, 8000, DATE '2020-11-11');

INSERT INTO nested_with_employees
VALUES (6, 'Fiona', 30, 4500, DATE '2024-04-12');

COMMIT;

Check the data before running the nested CTE examples:

SELECT *
FROM nested_with_departments
ORDER BY department_id;

Result:

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           10 Engineering
           20 Sales
           30 Support

SELECT *
FROM nested_with_employees
ORDER BY employee_id;

Result:

EMPLOYEE_ID EMPLOYEE_NAME DEPARTMENT_ID SALARY HIRE_DATE
----------- ------------- ------------- ------ ---------
          1 Anna                     10   5000 10-JAN-22
          2 Bob                      10   7000 15-MAY-21
          3 Chris                    10   9000 20-MAR-20
          4 Diana                    20   4000 01-FEB-23
          5 Eric                     20   8000 11-NOV-20
          6 Fiona                    30   4500 12-APR-24

Simple nested WITH example

The inner CTE selects employees earning at least 6000. The outer CTE joins this local result with the departments table and calculates totals:

WITH department_summary AS
(
  WITH high_earners AS
  (
    SELECT department_id,
           salary
    FROM nested_with_employees
    WHERE salary >= 6000
  )
  SELECT d.department_id,
         d.department_name,
         COUNT(h.salary) employee_count,
         SUM(h.salary) total_salary
  FROM nested_with_departments d
  JOIN high_earners h
    ON h.department_id = d.department_id
  GROUP BY d.department_id,
           d.department_name
)
SELECT *
FROM department_summary
ORDER BY department_id;

Result:

DEPARTMENT_ID DEPARTMENT_NAME EMPLOYEE_COUNT TOTAL_SALARY
------------- --------------- -------------- ------------
           10 Engineering                  2        16000
           20 Sales                        1         8000

high_earners is visible only inside the query defining department_summary. The final query sees department_summary, but it cannot reference the inner CTE directly.

Correlated nested WITH example

Oracle 26ai supports correlated and non-correlated nested WITH clauses. In the following example, the inner department_stats CTE references e.department_id from its containing query:

WITH above_department_average AS
(
  SELECT e.*
  FROM nested_with_employees e
  WHERE e.salary >
  (
    WITH department_stats AS
    (
      SELECT AVG(x.salary) average_salary
      FROM nested_with_employees x
      WHERE x.department_id = e.department_id
    )
    SELECT average_salary
    FROM department_stats
  )
)
SELECT employee_id,
       employee_name,
       department_id,
       salary
FROM above_department_average
ORDER BY employee_id;

Result:

EMPLOYEE_ID EMPLOYEE_NAME   DEPARTMENT_ID     SALARY
----------- --------------- ------------- ----------
          3 Chris                      10       9000
          5 Eric                       20       8000

For every employee, the nested CTE calculates the average salary for that employee’s department. Only employees earning more than their department average are returned.

More than two nesting levels

Nested CTEs are not limited to one inner level. The following three-level example also works:

WITH level_one AS
(
  WITH level_two AS
  (
    WITH level_three AS
    (
      SELECT 26 release_number
      FROM dual
    )
    SELECT release_number,
           'Nested level 2' description
    FROM level_three
  )
  SELECT release_number,
         description
  FROM level_two
)
SELECT *
FROM level_one;

Result:

RELEASE_NUMBER DESCRIPTION
-------------- --------------
            26 Nested level 2

Scope

An inner CTE is visible only inside its containing query block. It can reference outer columns; use table aliases to make correlated references clear.

Restrictions

Oracle AI Database 26ai does not support the following cases:

• a recursive WITH clause nested inside another WITH clause,
• a nested WITH clause inside a recursive WITH clause,
• PL/SQL function declarations inside a nested WITH clause,
• table value constructors inside a nested WITH clause,
• a WITH clause placed after a set operator such as UNION, INTERSECT, EXCEPT or MINUS.

Use a top-level CTE when several query blocks reuse it. Use a nested CTE for a helper needed by only one stage.

Cleanup

Drop the child table first because it contains the foreign key:

DROP TABLE nested_with_employees PURGE;
DROP TABLE nested_with_departments PURGE;

Summary

Oracle 26ai supports correlated and non-correlated nested CTEs. They provide local scope and easier SQL migration, subject to the restrictions listed above.

More details and the complete list of restrictions are available in the Oracle SELECT documentation and the Oracle AI Database 26ai New Features Guide.

Have a fun 🙂

  Tomasz