Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Issue with ANSI Join

3134376Mar 15 2019 — edited Mar 16 2019

Hi All,

Please clear my doubts with ANSI joins :

1. When to use LATERAL JOINS and how to use these with ANSI syntax ?

Let us see in below example how I can make it working with ANSI syntax inside LATERAL (As I have to use table name after JOIN keyword below is not working and if I use table name its incorrect right as I'll be scanning department table twice)(As I have to use table name after JOIN keyword below is not working and if I use table name its incorrect right as I'll be scanning department table twice)

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;

Issue using with ANSI syntax -

SELECT department_name

       , employee_name

FROM departments d

JOIN LATERAL

( SELECT employee_name

  FROM employees e JOIN e.department_id = d.department_id

)

order BY 1, 2;

2. Are RIGHT Outer Joins not good for performance ?

3. What is difference b/w CROSS APPLY and OUTER APPLY, when to use those ? These looks same to me.

Comments
Post Details
Added on Mar 15 2019
11 comments
841 views