Wednesday, 26 October 2016

LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 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.
  • 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.

SELECT department_name, employee_name
FROM   departments d,
       (SELECT employee_name
        FROM   employees e
        WHERE  e.department_id = d.department_id);

        WHERE  e.department_id = d.department_id)
                                 *
ERROR at line 5:
ORA-00904: "D"."DEPARTMENT_ID": invalid identifier

SQL>

A LATERAL inline view allows you to reference the table on the left of the inline view definition in the FROM clause, allowing the inline view to be correlated.

SELECT department_name, employee_name
FROM   departments d,
       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>
  • CROSS APPLY Join
The CROSS APPLY join is a variant of the ANSI CROSS JOIN. 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 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>

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.

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

-- Use the PTF in the join.
SELECT department_name, b.*
FROM   departments d
       CROSS APPLY (TABLE(get_tab(d.department_id))) b
ORDER BY 1, 2;

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

The following example uses the pipelined table function defined previously on the right side of a OUTER APPLY join.

SELECT department_name, b.*
FROM   departments d
       OUTER APPLY (TABLE(get_tab(d.department_id))) b
ORDER BY 1, 2;

DEPARTMENT_NAM   COLUMN_VALUE
--------------            ------------
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,
       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   "TEST"."DEPARTMENTS" "D",
       "TEST"."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   "TEST"."DEPARTMENTS" "D",
       "TEST"."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   "TEST"."DEPARTMENTS" "D",
       "TEST"."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 "TEST"."DEPARTMENTS" "D",
      LATERAL(
              (SELECT VALUE(KOKBF$0) "COLUMN_VALUE_0"
               FROM TABLE("TEST"."GET_TAB"("D"."DEPARTMENT_ID")) "KOKBF$0"
              )
             )(+) "VW_LAT_D4FD8C38"
ORDER BY "D"."DEPARTMENT_NAME","VW_LAT_D4FD8C38"."COLUMN_VALUE_0"