asked on

What is this Oracle Syntax question for {oj RIGHT OUTER JOIN} with brackets

Never saw this syntax before, it was generated from Oracle Discoverer (v 11.1.1.3.0), but it won't run in SQL.

What is this syntax:
FROM Student_Vw S52_313113
{oj HIGH_SCHOOLS S52_118794 RIGHT OUTER JOIN Student_DW S52_170368 ON S52_170368.HS_ID = S52_118794.HS_ID}

Pseudo-query
SELECT ****
FROM Table A,
Table B
WHERE A.id = B.student_id
AND A.id in (SELECT S52_313113.PRMI_ID
FROM Student_Vw S52_313113
{oj HIGH_SCHOOLS S52_118794 RIGHT OUTER JOIN Student_DW S52_170368 ON S52_170368.HS_ID = S52_118794.HS_ID}
WHERE ( ( S52_313113.ID = S52_170368.Student_ID ) )
AND ( S52_313113.ITL = 'N' )
AND ( S52_313113.CLASS BETWEEN 2019 AND 2023 )
AND ( S52_170368.STATUS_CODE NOT IN ('31','32','35','36') )
AND ( S52_313113.CODE NOT IN ('19','20','21','22','23','34') )
AND ( S52_313113.CODE NOT IN ('96','97','98','99') ) )

Peter Chan

See this example for right outer join

SELECT
    employee_id,
    last_name,
    first_name,
    order_id,
    status
FROM
    orders
RIGHT JOIN employees ON
    employee_id = salesman_id
    AND employee_id = 57;

Open in new window

Alex [***Alex140181***]

Many other examples plus explanations can be found here:
https://oracle-base.com/articles/misc/sql-for-beginners-joins

William Peck

ASKER

HuaMin Chen and Alex,

thanks for the reply. I'm not a beginner but somehow I've gotten by with only inner join and left join, for like 20 years. I supposed I should add this to my repertoire ...

However, I'm not so much asking about what a RIGHT OUTER JOIN does as I am the syntax with the brackets and "oj" ...
FROM Student_Vw S52_313113
{oj HIGH_SCHOOLS S52_118794 RIGHT OUTER JOIN Student_DW S52_170368 ON S52_170368.HS_ID = S52_118794.HS_ID}

I've never seen the brackets part that and that's what confused me.

Isn't it possible to switch around the order of the tables in the FROM clause and then use LEFT OUTER join? I've done a lot of SQL in my time but just have never had the need to do a RIGHT join

Alex [***Alex140181***]

Isn't it possible to switch around the order of the tables in the FROM clause and then use LEFT OUTER join? I've done a lot of SQL in my time but just have never had the need to do a RIGHT join

It will depend on where you want to start ;-) Maybe it's not even possible to "start" with the other table in order to use a left join instead.
Apart from that, the order of the tables (when joined correctly and properly) doesn't matter that much nowadays since the database internals (e.g. Oracle's CBO) will handle that for you ;-)

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

William Peck

ASKER

Alex, thanks again for the followup ... I've always been able to do what I want to do with LEFT JOIN - - - that's how I visualize the tables. I should try RIGHT OUTER join just to understand better how it works and to get it situated in my mind ...

slightwv - thanks for chiming in, as you often do ... yes, it does seem like something internal to Oracle Discoverer ...

I cleaned up the query with easier to follow aliases in the sub-select ... but in that query THREE tables are being joined, whereas in the traditional RIGHT OUTER JOIN (or any join) it's TWO tables (example below)

THREE tables in the RIGHT OUTER JOIN with funny braces ...

SELECT ****
FROM Table A,
            Table B
WHERE A.id = B.student_id
    AND   A.id in (SELECT aa.PRMI_ID
                          FROM Student_Vw aa
                                {oj HIGH_SCHOOLS bb RIGHT OUTER JOIN Student_DW cc ON cc.HS_ID = bb.HS_ID}
                          WHERE ( ( aa.ID = cc.Student_ID ) )
                            AND ( aa.ITL = 'N' )
                            AND ( aa.CLASS BETWEEN 2019 AND 2023 )
                            AND ( cc.STATUS_CODE NOT IN ('31','32','35','36') )
                            AND ( aa.CODE NOT IN ('19','20','21','22','23','34') )
                            AND ( aa.CODE NOT IN ('96','97','98','99') ) )

Open in new window

Typically, it's only TWO tables joined in RIGHT OUTER JOIN, as I understand it ...

SELECT d.department_name,
       e.employee_name     
FROM   employees e
       RIGHT OUTER JOIN departments d ON e.department_id = d.department_id
WHERE  d.department_id >= 30
ORDER BY d.department_name, e.employee_name;

DEPARTMENT_NAM EMPLOYEE_N
-------------- ----------
OPERATIONS
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

7 rows selected.

Open in new window

William Peck

ASKER

So it was Oracle Discoverer that had the crazy braces in the RIGHT OUTER JOIN

{oj HIGH_SCHOOLS bb RIGHT OUTER JOIN Student_DW cc ON cc.HS_ID = bb.HS_ID}

Open in new window

but what Oracle actually ran was pretty simple:
SELECT cc.PRMI_ID
FROM DWDBA.HIGH_SCHOOLS aa,
DWDBA.MID_SHIPMEN bb,
DWDBA.MIDS_MIDSHIPMEN_VW cc
WHERE bb.HS_ID = aa.HS_ID(+)
and cc.ID = bb.MIDSHIPMEN_ID

the Discoverer braces threw me off .. but thanks for checking in! I do need to get my head around RIGHT OUTER JOIN

William Peck

ASKER

Thanks again!