Oracle: How functional table works in cross join?

Question

I explored technique to unfold comma separated list in column into rows:

with tbl as (
             select 1 id, 'a,b'   lst from dual
   union all select 2 id, 'c'     lst from dual
   union all select 3 id, 'e,f,g' lst from dual)
select
  tbl.ID
  , regexp_substr(tbl.lst, '[^,]+', 1, lvl.column_value) elem
  , lvl.column_value lvl
from
  tbl
  , table(cast(multiset(
     select level from dual
     connect by level <= regexp_count(tbl.lst, ',')+1) as sys.odcinumberlist)) lvl;

Result is:

ID      ELEM    LVL
1       a       1
1       b       2
2       c       1
3       e       1
3       f       2
3       g       3

As you can see LVL depends on value of regexp_count, so second functional table in cross join is parametrized by first table.

How is it working? How is it called? Can I paramertize third table based on two preceding in cross join and so forth?

Is parametrization limited to cross join or can be applied in join syntax too?

Reference: Splitting string into multiple rows in Oracle

score 1 · Answer 1 · answered Mar 18 '18 at 16:58

From the documentation:

LATERAL
Specify LATERAL to designate subquery as a lateral inline view. Within a lateral inline view, you can specify tables that appear to the left of the lateral inline view in the FROM clause of a query. You can specify this left correlation anywhere within subquery (such as the SELECT, FROM, and WHERE clauses) and at any nesting level.

-- a variation of the query in your question ...
select
  dt.id
, dt.list
, regexp_substr( dt.list, '[^,]+', 1, dt2.lvl ) elements
, dt2.lvl
from (
    select 1 id, 'a,b' list  from dual union all
    select 2, 'c'            from dual union all
    select 3, 'e,f,g'        from dual
) dt, lateral (
    select level lvl from dual
    connect by level <= regexp_count(dt.list, ',') + 1 
) dt2
;

-- output
ID  LIST   ELEMENTS  LVL  
1   a,b    a         1    
1   a,b    b         2    
2   c      c         1    
3   e,f,g  e         1    
3   e,f,g  f         2    
3   e,f,g  g         3

Example with 3 tables:

--drop table t1 ;
--drop table t2 ;
--drop table t3 ;

-- tables/data
create table t1 
as
select 1 id, 'a' letter from dual union all
select 2, 'b' from dual union all
select 3, 'c' from dual ;

create table t2 
as
select 1 id, 'd' letter from dual union all
select 2, 'e' from dual union all
select 3, 'f' from dual ;

create table t3 
as
select 1 id, 'g' letter from dual union all
select 2, 'h' from dual union all
select 3, 'i' from dual ;

-- query
select *
from 
  t1
, lateral ( select letter from t2 where id = t1.id ) t2
, lateral ( select letter from t3 where id = t2.id )
;

-- output
ID  LETTER  LETTER  LETTER  
1   a       d       g       
2   b       e       h       
3   c       f       i

Also (using the same tables)

-- reference t1 <- t2, 
-- reference t1 and t2 <- t3
select *
from 
  t1
, lateral ( select letter from t2 where id = t1.id ) t2
, lateral ( select letter || t1.letter from t3 where id = t2.id )
;

-- output
ID  LETTER  LETTER  LETTER||T1.LETTER  
1   a       d       ga                 
2   b       e       hb                 
3   c       f       ic

Whereas a "standard" cross join would give us ...

select *
from 
  t1 cross join t2 cross join t3
; 

ID  LETTER  ID  LETTER  ID  LETTER  
1   a       1   d       1   g       
1   a       1   d       2   h       
1   a       1   d       3   i       
1   a       2   e       1   g       
1   a       2   e       2   h       
1   a       2   e       3   i   
...
-- 27 rows

Related topics: CROSS APPLY (see documentation and examples here).

Oracle: How functional table works in cross join?

1 Answers1