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...