By way of background, a while back I did video called “My New Favourite Join” on the LATERAL clause.
The reason I like it is that it lets you solve a problem by composing little pieces together rather than writing one enormous query.
Now this post is not about LATERAL, but I’ll show a quick LATERAL example to show how it leads to a cool new 26ai SQL enhancement. I’ll start with a simple example.
SQL> select d.deptno, d.dname
2 from dept d;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
4 rows selected.
Suppose we decide we also want the salary of the highest paid employee in each department. The traditional solution would be to build an aggregate query and join it back.
SQL> select d.deptno, d.dname, m.maxsal
2 from dept d,
3 ( select deptno, max(sal) maxsal
4 from emp
5 group by deptno
6 ) m
7 where m.deptno = d.deptno;
DEPTNO DNAME MAXSAL
---------- -------------- ----------
10 ACCOUNTING 5000
20 RESEARCH 3000
30 SALES 2850
3 rows selected.
It’s not particular intuitive that this is how the problem should have been solved, but it’s not too hard to throw together. That is until someone says…
“Actually, I’d like the employee number as well.”
Now you have to ramp your SQL skills up a notch. We have to join the aggregate back to EMP, deal with ties, group again…
SQL> select d.deptno, d.dname, m.maxsal, max(e.empno)
2 from dept d,
3 ( select deptno, max(sal) maxsal
4 from emp
5 group by deptno
6 ) m,
7 emp e
8 where m.deptno = d.deptno
9 and e.sal = m.maxsal
10 group by d.deptno, d.dname, m.maxsal;
DEPTNO DNAME MAXSAL MAX(E.EMPNO)
---------- -------------- ---------- ------------
30 SALES 2850 7698
20 RESEARCH 3000 7902
10 ACCOUNTING 5000 7839
3 rows selected.
Nothing is technically bad with this approach (or solution) but it doesn’t really reflect the way humans typically tackle a coding problem. Typically we build a solution in components and knit them together. Repeating the example from that mindset, our approach would be something like.
“OK, I can get a list of departments easily enough…”
SQL> select d.deptno, d.dname
2 from dept d;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
4 rows selected.
“…and for a known department, I can easily write a SQL to get the employee details I need”
SQL> select e.empno, e.sal
2 from emp e
3 where e.deptno = 10
4 order by sal desc
5 fetch first 1 row only;
EMPNO SAL
---------- ----------
7839 5000
LATERAL gives us a simple way to piece those two “standalone” queries together.
SQL> select d.deptno, d.dname, m.empno, m.sal
2 from dept d,
3 lateral(
4 select e.empno, e.sal
5 from emp e
6 where e.deptno = d.deptno
7 order by sal desc
8 fetch first 1 row only ) m;
DEPTNO DNAME EMPNO SAL
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 5000
20 RESEARCH 7788 3000
30 SALES 7698 2850
3 rows selected.
I like this style because we’re using a “human” step-by-step process to still come up with a relational set-based solution. (It becomes the database engine’s job to convert to a nice standard join/aggregation if appropriate)
The WITH clause is similar
Using WITH has always encouraged the same style. Common Table Expressions have let use use modular SQL building blocks to compose a larger final SQL solution.
Instead of writing one giant statement, we give meaningful names to intermediate steps.
SQL> with avgsal as
2 ( select deptno, avg(sal) asal
3 from emp
4 group by deptno )
5 select *
6 from avgsal;
DEPTNO ASAL
---------- ----------
20 2175
30 1566.66667
10 2916.66667
That’s much easier to read than burying everything inside nested inline views.
However, if I wanted to apply the same approach as LATERAL, ie, grab an existing query (that might contain a WITH) and then utilise in another SQL that contains a WITH, then I strike problems.
SQL> with my_outer_query as
2 (
3 with avgsal as
4 ( select deptno, avg(sal) asal
5 from emp
6 group by deptno )
7 select *
8 from avgsal
9 )
10 select * from my_outer_query;
with avgsal as
*
ERROR at line 3:
ORA-32034: unsupported use of WITH clause
New WITH in 26ai
Starting in Oracle Database 26ai, those restrictions disappear. Nested WITH clauses now work throughout SQL.
The previous example works exactly as you’d hope.
SQL> with w1 as
2 ( with w2 as
3 (select deptno, dname
4 from dept
5 where dname = 'SALES'
6 )
7 select dname, emp.*
8 from emp, w2
9 where emp.deptno = w2.deptno
10 )
11 select dname, empno, ename, job
12 from w1
13 order by empno;
DNAME EMPNO ENAME JOB
-------------- ---------- ---------- ---------
SALES 7499 ALLEN SALESMAN
SALES 7521 WARD SALESMAN
SALES 7654 MARTIN SALESMAN
SALES 7698 BLAKE MANAGER
SALES 7844 TURNER SALESMAN
SALES 7900 JAMES CLERK
Even more deeply nested examples become legal.
SQL> with w1 as
2 (with w2 as
3 (with w3 as (
4 select emp.*
5 from emp, dept
6 where emp.deptno = dept.deptno
7 and dname = 'SALES')
8 select *
9 from w3
10 where job = 'SALESMAN'
11 )
12 select empno, ename, job, mgr, hiredate, sal, comm, sal+comm as income
13 from w2
14 order by income desc
15 fetch first 2 rows with ties
16 )
17 select *
18 from w1
19 order by income desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM INCOME
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 2650
Pretty much anywhere you could have SQL query, that query can now contain a WITH clause
SQL> with w1 as
2 (select dname, emp.*
3 from emp, ( with w2 as
4 (select deptno, dname
5 from dept
6 where dname = 'SALES' )
7 select *
8 from w2 ) dept_v
9 where emp.deptno = dept_v.deptno
10 )
11 select dname, empno, ename, job
12 from w1
13 order by empno;
DNAME EMPNO ENAME JOB
-------------- ---------- ---------- ---------
SALES 7499 ALLEN SALESMAN
SALES 7521 WARD SALESMAN
SALES 7654 MARTIN SALESMAN
SALES 7698 BLAKE MANAGER
SALES 7844 TURNER SALESMAN
SALES 7900 JAMES CLERK
A bonus for SQL macros
One pleasant side effect appears with SQL macros. Whilst not directly related to the nested WITH enhancement, there has historically been issues when you start combining SQL macros and a WITH clause.
In 19c, SQL macros couldn’t be referenced inside a WITH clause.
SQL> create or replace function my_emp_with(p_empno number)
2 return varchar2 sql_macro is
3 begin
4 return 'select * from emp where empno = p_empno';
5 end;
6 /
Function created.
SQL>
SQL> with my_query as
2 ( select * from my_emp_with(7369) )
3 select *
4 from my_query;
with my_query as
*
ERROR at line 1:
ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported
In an upcoming release update of 26ai, the same code works without any issues.
SQL> create or replace function my_emp_with(p_empno number)
2 return varchar2 sql_macro is
3 begin
4 return 'select * from emp where empno = p_empno';
5 end;
6 /
Function created.
SQL>
SQL> with my_query as
2 ( select * from my_emp_with(7369) )
3 select *
4 from my_query;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
1 row selected.
Building for the next developer
The real benefit here comes in terms of maintainability. As much as we all like to write giant complex monolithic statements to show how smart we are 
, it’s generally going to be better for the next developer that comes along to look after your code, if you can build solutions from small, understandable SQL blocks. It’s easier to write, much easier to review, and much easier to maintain.