| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL query help
Pete schrieb:
>
> I need some help with this query problem:
> All responses greatly appreciated.
>
> I created simple tables to simplify what I am really
> trying to do to make the problem easier to solve.
>
> One table holds the state name and area name,
> the other contains days of rain or snow.
>
> Tables:
>
> SQL> select * from sa_states;
>
> STATE AREA
> -------------------- -----------------
> HI West
> CO West
> GA South
> TX South
> NY North
> IN North
>
> SQL> select * from sa_stat;
>
> STATE PREC
> -------------------- -----------
> HI Rain
> HI Rain
> GA Rain
> GA Snow
> TX Snow
> IN Snow
> IN Snow
>
> I would like to count the number of snow days per state and
> group by area and then state.
> Where a state has no record in the stat table I would like
> a zero to appear, I would also like a zero to appear if the
> state is in the stat table but only has rain.
>
> The query listed below gives me the proper count and includes
> states not in the stat table but because of the
> second where clause does not return states in the stat table
> with only rain (i.e. HI) that I want to show up in the results
> with a count of zero.
>
> Thanks alot for taking a look at this.
> Pete
>
> Current Query
> SQL> select sa_states.state, sa_states.area, nvl(count(sa_stat.prec),0)
> as snow
> 2 from sa_states, sa_stat
> 3 where sa_states.state=sa_stat.state(+)
> 4 and nvl(sa_stat.prec, 'Snow')='Snow'
> 5 group by sa_states.area, sa_states.state;
>
Take a look at the bulletin I added at the end. Keeping its contens in mind your query should look more like:
(Quick and dirty):
select
sa.state, sa.area, (count(nvl(s.prec),0)) snow from sa_states sa, (select * from sa_stat where sa_stat.prec='Snow' ) where sa.state=s.state(+) Guidelines for Using the Outer Join Syntax
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.
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 Tue Mar 02 1999 - 01:50:46 CST
 |
 |