DBA Hub

📋Steps in this guide1/5

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 (12.1)

Learn the new variations on inline views and joins available in Oracle 12c.

oracle 12cconfigurationintermediate
by OracleDba
16 views
1

Setup

The examples in this article require the following tables to be present. These tables are a variant of the and tables from the schema. You will see a lot of Oracle examples on the internet using the tables from the schema. You can find the original table definitions in the "$ORACLE_HOME/rdbms/admin/utlsampl.sql" script.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
--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;
2

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 column from the table, which results in a error. A inline view allows us to reference the table on the left of the inline view definition in the clause, allowing the inline view to be correlated. This is also known as left correlation. There are some restrictions related to this functionality listed in the SELECT documentation. A inline view can be used to implement a and joins, as shown below. The inclusion of and joins eases migration of applications built using SQL Server.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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>

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

CROSS APPLY Join

The join is a variant of the ANSI 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 can reference columns from tables in the clause to the left. The example below uses a correlated inline view. We get the same result with a or an . 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 and doing a similar thing. Notice the rows for the accounting department (10) have dropped out because the pipelined table function returns no rows for that department.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
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>

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>

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

OUTER APPLY Join

The join is a variant of the with correlation support. The usage is similar to the 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 join. We get the same result with a . The following example uses the pipelined table function defined previously on the right side of a join. There is also an example of doing a similar thing with a , but the clause looks odd because this example doesn't have a natural join condition. Notice, the accounting department is displayed, but has a NULL value associated with it.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
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>

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>

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

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 inline view. After the query transformation is complete, the final statement processed by the server is as follows. Trace a simple join. After the query transformation is complete, the final statement processed by the server is as follows. Trace a simple join. After the query transformation is complete, the final statement processed by the server is as follows. 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 join using a pipelined table function. Here we can see the query has been transformed to a inline view. The fact this has not been transformed further suggests there is actually something under the hood for inline views, rather than just a query transformation. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
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';

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"

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';

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"

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';

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"

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';

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"

Comments (0)

Please to add comments

No comments yet. Be the first to comment!