Re: SQL Solution Desired

PMG schrieb:
>
> Hi Matthias,
>
> I am not sure how to do what you suggest (it's pretty late here - I will try
> tomorrow). Can you elaborate?
>
> Thanks

Guidelines for Using the Outer Join
Syntax
RDBMS
RDBMS
Support V6.0
October 1990

      1 INTRODUCTION

              The purpose of this technical note is  to  provide  some
      guidelines  on  how  to  use the outer join facility provided by
      ORACLE.

      1.1  Outer Join Semantics - Definitions

              The following terms, used to describe the  operation  on
      outer joins, are defined :-

            'outer-join column' - a column reference followed by the
                                  symbol (+), e.g. EMPNO(+) and
                                  DEPT.DEPTNO(+) are outer join columns

            'simple predicate'  - a logical expression containing no
                                  AND's, OR's, or NOT's ( usually a
                                  simple relation such as A = B )

        'outer join predicate'  - a simple predicate containing one
                                  or more outer join columns



      2  OUTER JOIN SYNTAX - RULES

              An outer join predicate  may  only  contain  outer  join
      columns  from one table ( in other words, all outer join columns
      in a single outer join predicate must belong to the  same  table
      ).   This  means,  for  example, that the following statement is
      illegal :-

        EMP.EMPNO(+) = DEPT.DEPTNO(+)  -  outer join columns from
                                          two tables

              Also, if a column  in  a  predicate  is  an  outer  join
      column,  then all columns from the same table must be outer join
      columns in that predicate.  This means, for  example,  that  the
      following statement is illegal :-

         EMP.SAL + EMP.COMM(+) = SALGRADE.HIGH  - mixed columns from
                                                  one table

              In a predicate, the  table  referenced  with  a  (+)  is
      directly  'outer  joined'  to all other tables in the predicate.
      It is indirectly 'outer joined' to any  tables  to  which  these
      other tables are themselves 'outer joined'.  A predicate may not
      be directly or indirectly 'outer joined' to itself.  This means,
      for  example,  that  the  following  combination of predictes is
      illegal :-

              EMP.EMPNO(+)   = PERS.EMPNO
         AND  PERS.DEPTNO(+) = DEPT.DEPTNO
         AND  DEPT.JOB(+)    = EMP.JOB           - circular outer
                                                   join relationship



      3  OUTER JOIN EXECUTION

              For a given table, T, there may be both outer  join  and
      non-outer join predicates.  Execution occurs ( conceptually ) as
      follows :-

           1. The result of joining all tables mentioned in table T's
              outer join predicates is formed ( by recursive application
              of this algorithm ).

           2. For each row of the result, a set of composite rows is
              formed, each consisting of the original row in the
              result joined to a row in table T for which the composite
              row satisfies all of table T's outer join predicates.

           3. If a set of composite rows is the null set, a composite
              row is created consisting of the original row in the
              result joined to a row similar to those in table T, but
              with all values set to null.

           4. Rows that do not pass the non-outer join predicates
              are removed.

              This  may  be  summarised  as   follows.    Outer   join
      predicates  (  those  with  (+) after a column of table T ), are
      evaluated BEFORE table T is augmented with a null row.  The null
      row  is  added only if there are NO rows in table T that satisfy
      the  outer  join  predicates.   Non-outer  join  predicates  are
      evaluated AFTER table T is augmented with a null row (if needed)


      4  OUTER JOIN - RECOMMENDATIONS

              Certain types of  outer  joins  in  complicated  logical
      expressions  may  not  be  well  formed.  In general, outer join
      columns in predicates that are  branches  of  an  OR  should  be
      avoided.   Inconsistancies  between  the  branches of the OR can
      result in an ambiguous query, and this may not be detected.   It
      is  best  to  confine outer join columns to the top level of the
      'where' clause, or to nested AND's only.


      5  OUTER JOIN - ILLUSTRATIVE EXAMPLES

      5.1  Simple Outer Join

         SELECT ENAME, LOC
         FROM   DEPT, EMP
         WHERE  DEPT.DEPTNO = EMP.DEPTNO(+)

              The predicate is evaluated BEFORE null augmentation.  If
      there is a DEPT row for which there are no EMP rows, then a null
      EMP row is concatenated to the DEPT row.


      5.2  Outer Join With Simple Post-Join Predicates

         SELECT ENAME, LOC
         FROM   DEPT, EMP
         WHERE  DEPT.DEPTNO = EMP.DEPTNO(+)
         AND    EMP.DEPTNO IS NULL

              The second simple  predicate  is  avaluated  AFTER  null
      augmentation,  since  there  is no (+), removing rows which were
      not the result of null augmentation and hence leaving only  DEPT
      rows for which there was no corresponding EMP row.


      5.3  Outer Join With Additional Pre-Join Predicates

         SELECT ENAME, LOC
         FROM   DEPT, EMP
         WHERE  DEPT.DEPTNO = EMP.DEPTNO(+)
         AND    'CLERK'     = EMP.JOB(+)
         AND    EMP.DEPTNO IS NULL

              The predicate on EMP.JOB is evaluated at the  same  time
      as  the  one  on  EMP.DEPTNO  -  before null augmentation.  As a
      result, a null row is augmented to any DEPT row for which  there
      are no corresponding clerks's in the EMP table.  Therefore, this
      query displays departments containing no clerks.

              Note that it the  (+)  were  omitted  from  the  EMP.JOB
      predicate,  no  rows  would be returned.  In this case, both the
      EMP.JOB and EMP.DEPTNO IS NULL predicates  are  evaluated  AFETR
      the  outer  join,  and  there  can be no rows for which both are
      true.

================================================================================

                          << E N D   O F   B U L L E T I N >>

HTH
Matthias
--
grema_at_t-online.de

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden. Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899 Received on Fri Feb 26 1999 - 05:09:33 CST