SELECT B.emp AS boss, P.emp FROM OrgChart AS P LEFT OUTER JOIN 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:
ORGCHART Emp Boss ----- ----- Bill John Bill Jane Bill Sue John Jack JaneBill 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 JaneBut 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in July 2002