Ask the Expert

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?

    Requires Free Membership to View

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

This was first published in July 2002

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: