BACKGROUND
Consider this post from stackoverflow.
As seen above, Nesting of with clauses is not possible in oracle and while the examples in the answer are simple and straightforward allowing you to define multiple different subqueries, sometimes (and I know because I’ve been there) you actually need to or at least wish you would nest a with clause. It’s just so sad isn’t it 😔😓🥺
INTRODUCTION
Well worry not because oracle database 26ai is here to turn that frown upside down …
Starting from oracle database release 23.26.2.0.0 we now have the ability to nest with clauses just like in the example above. The docs state
“Oracle AI Database now supports nesting WITH clauses within other WITH clauses, removing the earlier restriction and allowing use anywhere a WITH clause is valid. However, recursive nesting, PL/SQL functions, and table value constructors inside nested WITH clauses remain unsupported. Existing hints (inline/materialize) can still be used but may be ignored in nested scenarios without affecting non-nested query blocks.”
SYNTAX
WITH outer_cte AS (
WITH inner_cte_1 AS (
-- query using base tables or parent CTE columns
),
inner_cte_2 AS (
-- query that can use inner_cte_1 or outer scope columns
)
SELECT ...
FROM inner_cte_2
)
SELECT ...
FROM outer_cte;
USAGE
Autoupgrade
Immediately I saw this feature I was excited to try it out so I went ahead and patched my 23.26.1.0.0 database using autoupgrade, the details of which I plan to share in a later post. It was my first time using autoupgrade so if you are curious like me and would like to try it out check out this youtube video and blog which I used for reference.
Sample data
In order to show how this feature is used let’s create some sample tables and insert data into them.
CREATE TABLE dept
( dept_id NUMBER,
dept_name VARCHAR2(32 CHAR),
CONSTRAINT dept_pk PRIMARY KEY (dept_id)
) ;
CREATE TABLE emp
( emp_id NUMBER
,emp_name VARCHAR2(64 CHAR)
,job VARCHAR2(32 CHAR)
,status CHAR(1)
,dept_id NUMBER
,mgr_id NUMBER
,CONSTRAINT emp_pk PRIMARY KEY ("EMP_ID")
,CONSTRAINT emp_status_ck CHECK (status IN ('A','I'))
,CONSTRAINT emp_dept_id_fk FOREIGN KEY (dept_id)
REFERENCES dept (dept_id)
,CONSTRAINT emp_mgr_id_fk FOREIGN KEY (mgr_id)
REFERENCES emp (emp_id)
);
REM INSERTING into DEPT
SET DEFINE OFF;
Insert into DEPT (DEPT_ID,DEPT_NAME) values (1,'IT');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (2,'HR');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (3,'SALES');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (4,'FINANCE');
Insert into DEPT (DEPT_ID,DEPT_NAME) values (5,'ENGINEERING');
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (2,'Leon Le','IT MGR','A',1,null);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (5,'Jack S','Finance MGR','A',4,null);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (7,'Scott T','HR MGR','A',2,null);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (12,'Luke Coley','Engr Admin','A',5,null);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (15,'Miles Breaker','Sales Mgr','A',3,null);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (1,'Harris','DBA','A',1,2);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (3,'Todd B','ME','A',5,12);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (4,'Ken G','Finance Analyst','A',4,5);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (6,'Nolan E','Sales Agent','A',3,7);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (8,'Lendy L','Benefits Advisor','A',2,7);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (9,'Jerry T','IE','A',5,12);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (10,'Mikayla K','IE','A',5,12);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (11,'Nolan W','Sysadmin','A',1,2);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (13,'Saky Devone','HR Focal','A',2,7);
Insert into EMP (EMP_ID,EMP_NAME,JOB,STATUS,DEPT_ID,MGR_ID) values (14,'Kedson Stop','IE','I',5,12);
COMMIT;
EXAMPLE
show all employees who are engineers
Suppose we want to display all employees who work in the engineering department, that can be done using the query below
SQL> SELECT
e.emp_id
,e.emp_name
,e.job
,d.dept_name
,decode(e.status,'A','ACTIVE','I','INACTIVE') emp_status
FROM emp e
JOIN TO ONE (dept d ON e.dept_id = d.dept_id)
WHERE e.dept_id = 5;
EMP_ID EMP_NAME JOB DEPT_NAME EMP_STATUS
_________ ______________ _____________ ______________ _____________
3 Todd B ME ENGINEERING ACTIVE
9 Jerry T IE ENGINEERING ACTIVE
10 Mikayla K IE ENGINEERING ACTIVE
12 Luke Coley Engr Admin ENGINEERING ACTIVE
14 Kedson Stop IE ENGINEERING INACTIVE
SQL>
SQL> show all active employees who are engineers
suppose the requirement changed and we do not want to see inactive employees;
“We want to display all active employees(defined by status = ‘A’) who work in the engineering department”
The query can be rewritten with another where clause as
SQL> SELECT
e.emp_id
,e.emp_name
,e.job
,d.dept_name
,decode(e.status,'A','ACTIVE','I','INACTIVE') emp_status
FROM emp e
JOIN TO ONE (dept d ON e.dept_id = d.dept_id)
WHERE e.dept_id = 5
AND e.status = 'A';
EMP_ID EMP_NAME JOB DEPT_NAME EMP_STATUS
_________ _____________ _____________ ______________ _____________
3 Todd B ME ENGINEERING ACTIVE
9 Jerry T IE ENGINEERING ACTIVE
10 Mikayla K IE ENGINEERING ACTIVE
12 Luke Coley Engr Admin ENGINEERING ACTIVE or perhaps using a cte as
SQL> with active_emps as (
SELECT
e.emp_id
,e.emp_name
,e.job
,d.dept_name
,decode(e.status,'A','ACTIVE','I','INACTIVE') emp_status
FROM emp e
JOIN TO ONE (dept d ON e.dept_id = d.dept_id)
WHERE e.status = 'A' )
SELECT * FROM active_emps
WHERE dept_name = 'ENGINEERING';
EMP_ID EMP_NAME JOB DEPT_NAME EMP_STATUS
_________ _____________ _____________ ______________ _____________
3 Todd B ME ENGINEERING ACTIVE
9 Jerry T IE ENGINEERING ACTIVE
10 Mikayla K IE ENGINEERING ACTIVE
12 Luke Coley Engr Admin ENGINEERING ACTIVE
SQL> Using a nested with
In oracle 26ai, a nested CTE could also be used to resolve this as shown below
WITH active_engineers AS (
WITH active_emps as (
SELECT
e.emp_id
,e.emp_name
,e.job
,d.dept_name
,decode(e.status,'A','ACTIVE','I','INACTIVE') emp_status
FROM emp e
JOIN TO ONE (dept d ON e.dept_id = d.dept_id)
WHERE e.status = 'A'
)
SELECT *
FROM active_emps
WHERE dept_name = 'ENGINEERING'
)
SELECT * FROM
active_engineers ;
EMP_ID EMP_NAME JOB DEPT_NAME EMP_STATUS
_________ _____________ _____________ ______________ _____________
3 Todd B ME ENGINEERING ACTIVE
9 Jerry T IE ENGINEERING ACTIVE
10 Mikayla K IE ENGINEERING ACTIVE
12 Luke Coley Engr Admin ENGINEERING ACTIVE Why is this relevant?
Instead of having every with clause at the same top level as required in previous versions of oracle, you can now tuck smaller helper queries inside the part of the query that actually needs them. Previously, the nested query would have had to be the first cte defined and then used later on in the next CTE to compute another value which is almost counter-intuitive in the way we typically define and write subqueries; bottom up instead of top down. In this example, we first make a list of active employees, then pick only the Engineering ones. By nesting these steps together, the query is easier to read and also write.
JOIN TO ONE?
You might have picked up on the join syntax used here join to one which is also a new introduction to 23.26.2. The docs state that it is a modern join syntax that simplifies FROM clauses while preventing common join mistakes that can silently produce wrong results (see example). Essentially it ensures that your join to another tables produce one result for each row and it returns an error if that’s not the case (or atleast that’s how I understand it, it’s new for crying out loud). I plan to play with it more and perhaps make a post on it. However for now there’s a great blog post on it here that’s very well written.
CONCLUSION
So to our dear friend from stack overflow, “who’s your daddy now!”. This is something I also once upon a time felt the need to use and was crushed when my query failed saying I cannot nest a CTE. However, as you can probably tell my feeling right now is that of excitement as I know that although I got around to getting my query to work differently at the time, there’s one less issue to be concerned about going forward. Hopefully you learned something new from this blog or were atleast made aware that there are some new things to learn(and watch if you haven’t yet), and kindly leave a comment if you are so inclined.
Thanks for reading, cheers 🎊
Harris.