I often see what what devs want is a "scalar subquery" that returns multiple columns, like this:
select
d.deptno, d.dname
, case
when d.dname = 'SALES' then
(
-- DOES NOT WORK
select count(*) as cnt_emp, max(sal) as max_sal
from scott.emp e
where e.deptno = d.deptno
)
end as sales_info
from scott.dept d
order by d.deptno;
Which does not work - it raises error ORA-00913: too many values.
That's where LATERAL in my opinion is a great alternative, like this (using classic Oracle (+) syntax):
select
d.deptno, d.dname
, sales_info.cnt_emp, sales_info.max_sal
from scott.dept d, LATERAL (
select count(*) as cnt_emp, max(sal) as max_sal
from scott.emp e
where d.dname = 'SALES'
and e.deptno = d.deptno
)(+) sales_info
order by d.deptno;
Or the same using the ANSI syntax OUTER APPLY:
select
d.deptno, d.dname
, sales_info.cnt_emp, sales_info.max_sal
from scott.dept d
OUTER APPLY (
select count(*) as cnt_emp, max(sal) as max_sal
from scott.emp e
where d.dname = 'SALES'
and e.deptno = d.deptno
) sales_info
order by d.deptno;
Both work fine and give the result that is the intention of the non-working scalar subquery version above.
Lateral inline views are not much used in the wild, but work great for cases like this.