| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: outer join
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.
iashraf_at_csc.c
om To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_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: iashraf_at_csc.com
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: ListGuru_at_fatcity.com (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: Rick_Cale_at_teamhealth.com
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: ListGuru_at_fatcity.com (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).
Received on Tue Mar 12 2002 - 08:28:29 CST
 |
 |