Problem solve Get help with specific problems with your technologies, process and projects.

Trying to get a LEFT OUTER JOIN to work in 8i

I'm having difficulty translating this SQL statement into an Oracle 8i SQL statement (LEFT OUTER JOIN WITH SUBQUERY):

 SELECT B.emp AS boss, P.emp
   FROM OrgChart AS P
        OrgChart AS B
        ON B.lft
           = (SELECT MAX(lft)
                FROM OrgChart AS S
               WHERE P.lft > S.lft
                 AND P.lft < S.rgt);
Can this be achieved in Oracle SQL?

Yes it can, but not using the LEFT OUTER JOIN. Oracle does not support the LEFT OUTER JOIN syntax until Oracle 9i. Until then, you'll have to use the '(+)' symbol to denote outer joins. For example, assume we have the following table:

Emp     Boss
-----   -----
John    Bill
Jane    Bill
Sue     John
Jack    Jane
Bill has no boss since he owns the company. If we did a normal join on this table to itself, any records that do not participate in the join will not be returned. So the following query returns the following result:
SELECT p.emp, b.emp AS boss
FROM orgchart p, orgchart b
WHERE p.boss = b.emp;

Emp     Boss
------  -----
John    Bill
Jane    Bill
Sue     John
Jack    Jane
But we want Bill returned as well. So we use the outer join symbol. The outer join symbol only differentiates from a LEFT or RIGHT outer join by where it is placed in the statement.
SELECT p.emp, b.emp AS boss
FROM orgchart p, orgchart b (+)
WHERE p.boss = b.emp (+);
In the FROM clause, the '(+)' symbol tells the database that this table may need to have rows "filled in" in the join operation. And the '(+)' symbol is added in the WHERE clause for the entries that will appear as NULL values in the outer join operation.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.