# 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:

```SQL
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

```SQL
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:

```SQL
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:

```SQL
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:

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