8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 (12.1)
Oracle 12c introduced the LATERAL inline view syntax, as well as CROSS APPLY and OUTER APPLY joins into the SELECT syntax. There is some similarity between them, so it's easier to deal with them in a single article.
In all cases these features allow left correlation inside inline views. The examples here are very small, so you are unlikely to see a benefit using left correlation, but in some cases having extra predicates in the inline view will allow data to be filtered more quickly, before applying subsequent joins.
The optimizer has used LATERAL inline views during some query transformations for some time, but they were not documented and therefore not supported for us to use directly until now. Thanks to Dominic Brooks and Sayan Malakshinov for the clarification.
Related articles.
Setup
The examples in this article require the following tables to be present.
--drop table employees purge;
--drop table departments purge;
create table departments (
department_id number(2) constraint departments_pk primary key,
department_name varchar2(14),
location varchar2(13)
);
insert into departments values (10,'ACCOUNTING','NEW YORK');
insert into departments values (20,'RESEARCH','DALLAS');
insert into departments values (30,'SALES','CHICAGO');
insert into departments values (40,'OPERATIONS','BOSTON');
commit;
create table employees (
employee_id number(4) constraint employees_pk primary key,
employee_name varchar2(10),
job varchar2(9),
manager_id number(4),
hiredate date,
salary number(7,2),
commission number(7,2),
department_id number(2) constraint emp_department_id_fk references departments(department_id)
);
insert into employees values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into employees values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into employees values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into employees values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into employees values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into employees values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into employees values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into employees values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into employees values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into employees values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into employees values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into employees values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into employees values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into employees values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
These tables are a variant of the EMP and DEPT tables from the SCOTT schema. You will see a lot of Oracle examples on the internet using the tables from the SCOTT schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.
LATERAL Inline Views
Normally, it is not possible to reference tables outside of an inline view definition. In this example we try to reference the DEPARTMENT_ID column from the DEPARTMENTS table, which results in a error.
select department_name, employee_name
from departments d
cross join (select employee_name
from employees e
where e.department_id = d.department_id)
order by 1, 2;
ERROR at line 5:
ORA-00904: "D"."DEPARTMENT_ID": invalid identifier
SQL>
A LATERAL inline view allows us to reference the table on the left of the inline view definition in the FROM clause, allowing the inline view to be correlated. This is also known as left correlation.
select department_name, employee_name
from departments d
cross join lateral (select employee_name
from employees e
where e.department_id = d.department_id)
order by 1, 2;
DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
RESEARCH ADAMS
RESEARCH FORD
RESEARCH JONES
RESEARCH SCOTT
RESEARCH SMITH
SALES ALLEN
SALES BLAKE
SALES JAMES
SALES MARTIN
SALES TURNER
SALES WARD
14 rows selected.
SQL>
There are some restrictions related to this functionality listed in the SELECT documentation.
A LATERAL inline view can be used to implement a CROSS APPLY and OUTER APPLY joins, as shown below. The inclusion of CROSS APPLY and OUTER APPLY joins eases migration of applications built using SQL Server.
CROSS APPLY Join
The CROSS APPLY join is a variant of the ANSI CROSS JOIN with correlation support. It returns all rows from the left hand table, where at least one row is returned by the table reference or collection expression on the right. The right side of the APPLY can reference columns from tables in the FROM clause to the left. The example below uses a correlated inline view.
select department_name, employee_id, employee_name
from departments d
cross apply (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
DEPARTMENT_NAM EMPLOYEE_ID EMPLOYEE_N
-------------- ----------- ----------
ACCOUNTING 7782 CLARK
ACCOUNTING 7839 KING
RESEARCH 7566 JONES
RESEARCH 7788 SCOTT
RESEARCH 7902 FORD
SALES 7698 BLAKE
6 rows selected.
SQL>
We get the same result with a CROSS JOIN LATERAL or an INNER JOIN LATERAL.
select department_name, employee_id, employee_name
from departments d
cross join lateral (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
DEPARTMENT_NAM EMPLOYEE_ID EMPLOYEE_N
-------------- ----------- ----------
ACCOUNTING 7782 CLARK
ACCOUNTING 7839 KING
RESEARCH 7566 JONES
RESEARCH 7788 SCOTT
RESEARCH 7902 FORD
SALES 7698 BLAKE
6 rows selected.
SQL>
select department_name, employee_id, employee_name
from departments d
inner join lateral (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id) e
on e.department_id = d.department_id
order by 1, 2, 3;
DEPARTMENT_NAM EMPLOYEE_ID EMPLOYEE_N
-------------- ----------- ----------
ACCOUNTING 7782 CLARK
ACCOUNTING 7839 KING
RESEARCH 7566 JONES
RESEARCH 7788 SCOTT
RESEARCH 7902 FORD
SALES 7698 BLAKE
6 rows selected.
SQL>
The following example uses a pipelined table function on the right side of the join. Notice, it too is correlated as it uses a column from the left side table as a parameter into the function. There is also an example of a CROSS JOIN LATERAL and INNER JOIN LATERAL doing a similar thing.
-- Create the type and PTF.
create type t_tab as table of number;
/
create or replace function get_tab (p_department_id in number)
return t_tab pipelined
as
begin
if p_department_id != 10 then
for i in (select level as numval
from dual
connect by level <= 2)
loop
pipe row (i.numval);
end loop;
end if;
return;
end;
/
-- CROSS APPLY
select department_name, b.*
from departments d
cross apply (table(get_tab(d.department_id))) b
order by 1, 2;
DEPARTMENT_NAM Result Sequence
-------------- ---------------
OPERATIONS 1
OPERATIONS 2
RESEARCH 1
RESEARCH 2
SALES 1
SALES 2
6 rows selected.
SQL>
-- CROSS JOIN LATERAL
select department_name, b.*
from departments d
cross join lateral (select * from table(get_tab(d.department_id))) b
order by 1, 2;
DEPARTMENT_NAM Result Sequence
-------------- ---------------
OPERATIONS 1
OPERATIONS 2
RESEARCH 1
RESEARCH 2
SALES 1
SALES 2
6 rows selected.
SQL>
-- INNER JOIN LATERAL
select department_name, b.*
from departments d
inner join lateral (select * from table(get_tab(d.department_id))) b
on 1 = 1
order by 1, 2;
DEPARTMENT_NAM Result Sequence
-------------- ---------------
OPERATIONS 1
OPERATIONS 2
RESEARCH 1
RESEARCH 2
SALES 1
SALES 2
6 rows selected.
SQL>
Notice the rows for the accounting department (10) have dropped out because the pipelined table function returns no rows for that department.
OUTER APPLY Join
The OUTER APPLY join is a variant of the LEFT OUTER JOIN with correlation support. The usage is similar to the CROSS APPLY join, but it returns all rows from the table on the left side of the join. If the right side of the join returns no rows, the corresponding columns in the output contain NULLs. The following is a repeat of the example from the previous section, with the join clause changed to an OUTER APPLY join.
select department_name, employee_id, employee_name
from departments d
outer apply (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
DEPARTMENT_NAM EMPLOYEE_ID EMPLOYEE_N
-------------- ----------- ----------
ACCOUNTING 7782 CLARK
ACCOUNTING 7839 KING
OPERATIONS
RESEARCH 7566 JONES
RESEARCH 7788 SCOTT
RESEARCH 7902 FORD
SALES 7698 BLAKE
7 rows selected.
SQL>
We get the same result with a LEFT [OUTER] JOIN LATERAL.
select department_name, employee_id, employee_name
from departments d
left join lateral (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id) e
on e.department_id = d.department_id
order by 1, 2, 3;
DEPARTMENT_NAM EMPLOYEE_ID EMPLOYEE_N
-------------- ----------- ----------
ACCOUNTING 7782 CLARK
ACCOUNTING 7839 KING
OPERATIONS
RESEARCH 7566 JONES
RESEARCH 7788 SCOTT
RESEARCH 7902 FORD
SALES 7698 BLAKE
7 rows selected.
SQL>
The following example uses the pipelined table function defined previously on the right side of a OUTER APPLY join. There is also an example of doing a similar thing with a LEFT [OUTER] JOIN LATERAL, but the ON clause looks odd because this example doesn't have a natural join condition.
-- OUTER APPLY
select department_name, b.*
from departments d
outer apply (table(get_tab(d.department_id))) b
order by 1, 2;
DEPARTMENT_NAM Result Sequence
-------------- ---------------
ACCOUNTING
OPERATIONS 1
OPERATIONS 2
RESEARCH 1
RESEARCH 2
SALES 1
SALES 2
7 rows selected.
SQL>
-- LEFT JOIN LATERAL
select department_name, b.*
from departments d
left join lateral (select * from table(get_tab(d.department_id))) b
on 1=1
order by 1, 2;
DEPARTMENT_NAM Result Sequence
-------------- ---------------
ACCOUNTING
OPERATIONS 1
OPERATIONS 2
RESEARCH 1
RESEARCH 2
SALES 1
SALES 2
7 rows selected.
SQL>
Notice, the accounting department is displayed, but has a NULL value associated with it.
Query Transformations
You might be looking at this functionality and thinking it looks like a minor variation on the existing join syntax. Doing a 10053 trace on some of these simple examples shows how Oracle is actually processing the statements.
Trace a simple LATERAL inline view.
alter session set events '10053 trace name context forever';
select department_name, employee_name
from departments d
cross join lateral (select employee_name
from employees e
where e.department_id = d.department_id)
order by 1, 2;
alter session set events '10053 trace name context off';
After the query transformation is complete, the final statement processed by the server is as follows.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
"E"."EMPLOYEE_NAME" "EMPLOYEE_NAME"
FROM "TESTUSER1"."DEPARTMENTS" "D",
"TESTUSER1"."EMPLOYEES" "E"
WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
ORDER BY "D"."DEPARTMENT_NAME","E"."EMPLOYEE_NAME"
Trace a simple CROSS APPLY join.
alter session set events '10053 trace name context forever';
select department_name, employee_id, employee_name
from departments d
cross apply (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
ALTER SESSION SET EVENTS '10053 trace name context off';
After the query transformation is complete, the final statement processed by the server is as follows.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
"E"."EMPLOYEE_ID" "EMPLOYEE_ID",
"E"."EMPLOYEE_NAME" "EMPLOYEE_NAME"
FROM "TESTUSER1"."DEPARTMENTS" "D",
"TESTUSER1"."EMPLOYEES" "E"
WHERE "E"."SALARY">=2000
AND "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"
ORDER BY "D"."DEPARTMENT_NAME","E"."EMPLOYEE_ID","E"."EMPLOYEE_NAME"
Trace a simple OUTER APPLY join.
alter session set events '10053 trace name context forever';
select department_name, employee_id, employee_name
from departments d
outer apply (select employee_id, employee_name
from employees e
where salary >= 2000
and e.department_id = d.department_id)
order by 1, 2, 3;
alter session set events '10053 trace name context off';
After the query transformation is complete, the final statement processed by the server is as follows.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
"E"."EMPLOYEE_ID" "EMPLOYEE_ID",
"E"."EMPLOYEE_NAME" "EMPLOYEE_NAME"
FROM "TESTUSER1"."DEPARTMENTS" "D",
"TESTUSER1"."EMPLOYEES" "E"
WHERE "E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID"
AND "E"."SALARY"(+)>=2000
ORDER BY "D"."DEPARTMENT_NAME","E"."EMPLOYEE_ID","E"."EMPLOYEE_NAME"
In the case of these simple examples, this is just a basic query transformation, allowing us to use an alternate syntax. Of course, that's not to say there isn't something more interesting under the hood for other cases.
Trace something a little more exotic, like the OUTER APPLY join using a pipelined table function.
alter session set events '10053 trace name context forever';
select department_name, b.*
from departments d
outer apply (table(get_tab(d.department_id))) b
order by 1, 2;
alter session set events '10053 trace name context off';
Here we can see the query has been transformed to a LATERAL inline view. The fact this has not been transformed further suggests there is actually something under the hood for LATERAL inline views, rather than just a query transformation.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "D"."DEPARTMENT_NAME" "DEPARTMENT_NAME",
"VW_LAT_D4FD8C38"."COLUMN_VALUE_0" "COLUMN_VALUE"
FROM "TESTUSER1"."DEPARTMENTS" "D",
LATERAL( (SELECT VALUE(KOKBF$0) "COLUMN_VALUE_0"
FROM TABLE("TESTUSER1"."GET_TAB"("D"."DEPARTMENT_ID")) "KOKBF$0"))(+) "VW_LAT_D4FD8C38"
ORDER BY "D"."DEPARTMENT_NAME","VW_LAT_D4FD8C38"."COLUMN_VALUE_0"
For more information see:
- SELECT
- Oracle Pipelined Table Functions
- ANSI/ISO SQL Support In Oracle 9i
- Greg Rahn : ANSI Outer Joins And Lateral Views
- Jonathan Lewis : ANSI Outer
- Jonathan Lewis : ANSI Outer 2
Hope this helps. Regards Tim...