Introduction to JOIN LATERAL on Oracle

Lateral join joins a subquery, which is executed as many times as there are rows in the leading table. Consider it as a kind of for-each loop. Most of, if not all, the problems it solves, can also be solved without lateral join (e.g. using analytic functions).

Those alternatives can sometimes yield a better execution plan and lateral join can sometimes yield a more readable code. So, as with anything - it depends. Let's consider a few examples.

Example

Suppose we'd like to get a list of all departments (DEPT) and for each one we'd also like to display salary and commission of an employee (EMP), who was the first hire in a given department:

select /*+ GATHER_PLAN_STATISTICS */ d.*, x.sal, x.comm
   from dept d
   join lateral (
      select sal, comm
         from emp e 
         where e.deptno = d.deptno
         order by e.hiredate asc
         fetch first 1 row only
   ) x on 1=1;

    DEPTNO DNAME          LOC                SAL    COMM
---------- -------------- ------------- -------- -------
        10 ACCOUNTING     NEW YORK       2450.00
        20 RESEARCH       DALLAS          800.00
        30 SALES          CHICAGO        1600.00  300.00

Explain Plan

select * from table(
    dbms_xplan.display_cursor('20p9d49b4q2j7', 0, 'ALLSTATS'));

-----------------------------------------------------------------------
| Id  | Operation                 | Name            | Starts | A-Rows |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |      1 |      3 |
|   1 |  NESTED LOOPS             |                 |      1 |      3 |
|   2 |   TABLE ACCESS FULL       | DEPT            |      1 |      4 |
|   3 |   VIEW                    | VW_LAT_2D0B8FC8 |      4 |      3 |
|*  4 |    COUNT STOPKEY          |                 |      4 |      3 |
|   5 |     VIEW                  |                 |      4 |      3 |
|*  6 |      SORT ORDER BY STOPKEY|                 |      4 |      3 |
|*  7 |       TABLE ACCESS FULL   | EMP             |      4 |     14 |
-----------------------------------------------------------------------

dbms_xplan.display_cursor actually returns more columns - I'm only displaying Starts and A-Rows here to make a point. Those two rows are only available in execution plan output if you use /*+ GATHER_PLAN_STATISTICS */ (as I did in this example) or set statistics_level=ALL.

Letter "A" in A-Rows stands for Actual - it is the actual number of rows returned by the operation.

Starts tells us how many times the subquery started executing. So, according to line with Id=2, full table scan on DEPT ran once and returned 4 rows. In line Id=3 we see that our subquery was executed 4 times. All those executions together returned 3 rows (one of the departments has no employees, which is why line with Id=1 says only 3 rows were returned after JOIN).

Alternatives

Let's consider a few alternative solutions to our JOIN LATERAL example, just to give you ideas on what you can try if you find yourself hunting for a different execution plan. None of those is best; you can only get the best one if you study your specific execution plans and maybe compare the amount of buffer gets required.

Scalar Subqueries

The query that sparked the initial debate which eventually lead to this blog post was something like this:

select d.*,
      (select x.sal  from emp x where x.deptno = d.deptno order by x.hiredate asc fetch first 1 row only) as sal,
      (select x.comm from emp x where x.deptno = d.deptno order by x.hiredate asc fetch first 1 row only) as comm,
      /* ... */
   from dept d;

It can be "bad" (performance-wise) to execute subquery for each row, but it is twice as "bad" to do it twice for each row. Notice that JOIN LATERAL version accesses EMP table half as many times. Also, using expressions such as this example can only return 1 row whereas join lateral subquery can return any number of rows.

Eagle-eyed observers will probably also notice that our initial example would require left join lateral to match results of this alternative.

Analytic Functions

Using analytic functions we can read both tables only once, like this:

WITH emp_w AS (
   SELECT deptno, sal, comm,
          ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY hiredate ASC) AS rn
    FROM emp
)
SELECT d.*, e.sal, e.comm
FROM dept d
LEFT JOIN emp_w e ON d.deptno = e.deptno AND e.rn = 1;

Correlated Subquery

This one may be the simplest one to understand:

SELECT d.*, e.sal, e.comm
FROM dept d
JOIN emp e ON d.deptno = e.deptno
AND e.hiredate = (SELECT MIN(hiredate)
                  FROM emp
                  WHERE deptno = d.deptno);

Final Thoughts

JOIN LATERAL is a powerful feature supported by many relational databases as it is also part of ANSI standard. On Oracle, it is supported since version 12c. However, I do believe that it is important to be aware of basic performance aspects of its usage.