Hi,
Perhaps this note may help.
Rgds
Rick
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
contain outer
join
columns from
two or more
tables but may not
contain a
circular
reference. (
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.DEPTNO(+) =
DEPT.DEPTNO(+) -
outer join
columns from
two tables
The following
Outer-join
predicate is
allowed:-
FROM EMP,
DEPT, REGION
WHERE
EMP.ENAME =
DEPT.DETPNO(+)
AND
DEPT.REGION_NAME
= REGION.NAME(+)
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.
[EMAIL PROTECTED]
om To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Sent by: cc:
root@fatcity. Subject: outer join
com
03/12/2002
08:23 AM
Please
respond to
ORACLE-L
Hi,
im trying to create a view comprising of about 10 tables, trying to join
them together.i need a few outer jojns, but i have been told this is not
possible, i.e. having numerous outer joins in the where clause..
anyone got any info on these rules for outer joins?
cheers
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
