Home » SQL & PL/SQL » SQL & PL/SQL » outer joins
outer joins [message #9175] Fri, 24 October 2003 09:40 Go to next message
Rock
Messages: 18
Registered: February 2002
Junior Member
can someone point me a good document on writing outer joins in oracle . Im not talking about ansi compliant syntaxes in 9i but old syntaxes.

Thanks in advance.
Rock.
Re: outer joins [message #9178 is a reply to message #9175] Fri, 24 October 2003 10:09 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Re: outer joins [message #9179 is a reply to message #9178] Fri, 24 October 2003 10:17 Go to previous messageGo to next message
Rock
Messages: 18
Registered: February 2002
Junior Member
Thanks Art, but i was looking for some more details explaination like how will an left outer/right outer join will behave if there are more than one table involved in the join.

Tia
-R
Re: outer joins [message #9180 is a reply to message #9179] Fri, 24 October 2003 10:40 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
quote from :
----------------------------------------------------------------------
Thanks Art, but i was looking for some more details explaination like how will an left outer/right outer join will behave if there are more than one table involved in the join.

Tia
-R

----------------------------------------------------------------------
Oh, your original post didn't happen to mention that.

Can you give a specific example of what you're trying to do?

When it comes to outer joins in Oracle, there are usually two gotchas I run into:
  • ORA-01417 errors;
  • The invalidation of outer joins by not chaining them correctly.
To fix the ORA-1417, first create an in-line view, and then outer-join to that. The error description covers this fix pretty well:
$ oerr ora 1417
01417, 00000, "a table may be outer joined to at most one other table"
// *Cause:  a.b (+) = b.b and a.c (+) = c.c is not allowed
// *Action: Check that this is really what you want, then join b and c first
//          in a view.
$
As for the second "gotcha". You have to chain your outer joins. For example, this query
SQL> SELECT   d.dname
  2  ,        COUNT(e.ename)
  3  FROM     emp  e
  4  ,        dept d
  5  WHERE    d.deptno = e.deptno (+)
  6  AND      e.ename > CHR(32)
  7  GROUP BY d.dname
  8  /
will not produce this
DNAME          COUNT(E.ENAME)
-------------- --------------
ACCOUNTING                  3
OPERATIONS                  0
RESEARCH                    5
SALES                       6
but instead will produce this
DNAME          COUNT(E.ENAME)
-------------- --------------
ACCOUNTING                  3
RESEARCH                    5
SALES                       6
To get the former, you will have to chain your outer joins:
SQL> SELECT   d.dname
  2  ,        COUNT(e.ename)
  3  FROM     emp  e
  4  ,        dept d
  5  WHERE    d.deptno = e.deptno (+)
  6  AND      e.ename <font color=red><b>(+)</b></font> > CHR(32)
  7  GROUP BY d.dname
  8  /
  
DNAME          COUNT(E.ENAME)
-------------- --------------
ACCOUNTING                  3
OPERATIONS                  0
RESEARCH                    5
SALES                       6
  
SQL> 
See here for an explanation.

If these points do not cover your issue, Rock, please provide a simple example.

HTH,

Art.
Re: outer joins [message #9181 is a reply to message #9180] Fri, 24 October 2003 12:02 Go to previous message
Rock
Messages: 18
Registered: February 2002
Junior Member
thanks a lot.
Previous Topic: performance prob.
Next Topic: Pl-sql package.
Goto Forum:
  


Current Time: Fri Apr 26 13:28:10 CDT 2024