How to use Lateral join in Oracle?

Question

I am trying to use Lateral Join in Oracle(Oracle Database 11g Release 11.2.0.1.0) but it is giving some error. I have followed this link

https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1#lateral-inline-views and applied it on the same data but still it isn't working. Can someone figure out the issue?

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;

Adding further details for clarify why I need a lateral join: I have a table e.g

and I want to add another column of the sum of the length of records that have ID less than current row's ID i.e

ID  Length   Sum 
1   20       NULL
2   50       20
3   30       70
4   40       100
5   20       140
6   80       160

With the Lateral JOIN it could have be very simple for example

select A.ID,A.length,Sum from Table A,
Lateral (select sum(B.length) as Sum from Table B where B.id<A.id);

So is there is any alternative to this?

The fact that it's a 12c new feature is mentioned in the URL (twice), the article heading *"LATERAL Inline Views, CROSS APPLY and OUTER APPLY Joins in Oracle Database 12c Release 1 (12.1)"*, and the first paragraph: *"Oracle 12c introduced the LATERAL inline view syntax"*. Could that be the issue? — William Robertson, Jan 22 '20 at 22:39

VBoka · Accepted Answer · 2020-01-21T15:46:23.533

The LATERAL does not work because it is introduced from version 12. As GMB say. This is one aproach to the problem you have:

SELECT t1.id, t3.name
FROM   test t1
left join (select id, name from test t2) t3
on t1.id = t3.id
order by 2, 1

Here is a DEMO

Or maybe you wanted something like this:

select
   t1.id,
   t1.name
from   test t1
       where t1.name in (select t2.name
                from   test t2
                where  t2.id = t1.id)
order by 1, 2;

If none of the above approaches does not help you (it is not what you wanted), then there is another way. You can "enable" LATERAl in your old Oracle 11 version like this:

alter session set events '22829 trace name context forever';

You see, this option/feature did existed in older versions but it was not "enabled". Here is a DEMO showing that your statement on your example data first trhows an error and then after this alter session command, it works.

If you want a query that will give you a resul as in your question and will work on 11g then you can use this:

select ID, Length, LAG(ACUM) OVER (order by ID) sum
from (SELECT ID
             , length
             , Sum(length) OVER (ORDER BY id) as ACUM
      FROM   Table1 
      group by ID, length)

And the same thing can be done in a "more easy" way:

SELECT id,
       length,
       (SELECT Sum(length)
        FROM   Table1 b
        WHERE  a.id > b.id) ACUM
FROM   Table1 a

Here is the demo where you can see this query returns the same ersults.

Hope this helps.

Hi @Ayaz49. Great, you are welcome. I am happy to help. Thank you for your feedback. Cheers! — VBoka, Jan 22 '20 at 11:10

score 1 · Answer 2 · answered Jan 21 '20 at 12:54

1

Your query is fine, however the LATERAL JOIN syntax was added in Oracle 12.1, so it is not available for 11.2 version, that you are running.

For your use case, you can use a regular join:

SELECT d.department_name, e.employee_name
FROM department d
INNER JOIN employee e ON e.department_id = d.department_id
ORDER BY 1, 2

answered Jan 21 '20 at 12:54

GMB

216,147
25
84
135

Yes this works but I need to implement something similar to Lateral JOIN. – Ayaz49 Jan 21 '20 at 13:00
@Ayaz49: well, `LATERAL JOIN` is just not available. What do you call something *similar to LATERAL JOIN*? The above query produces identitical results. – GMB Jan 21 '20 at 13:01

How to use Lateral join in Oracle?

2 Answers2