Re: Problem by query with subquery

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:SpJAa.44368$1s1.597763_at_newsfeeds.bigpond.com...
> "Andrei Romazanov" <ierdna_at_web.de> wrote in message
> news:bavhq3$42bba$1_at_ID-70985.news.dfncis.de...
> > Hello all,
> >
> > i have a query 1 with subquery:
> >
> > SELECT A.F2,B.F2,C.F3 FROM A,B,(SELECT F3 FROM TAB) C
> >
> > the query 2 (without subquery ) SELECT A.F2,B.F2 FROM A,B selects some
> rows,
> > the query 3 (subquery ) SELECT F3 FROM TAB - no rows.
> >
> > I thought that the query 1 selects the same rows as query 2, but it
> selects
> > no rows!!!
> > If I manipulate the data so that query 3 selects some rows , than
selects
> > query 1 also some rows. I often work with subqueries, but I did not see
> this
> > behavior yet. Could someone explain this problem?
> >
> > ORACLE 8.1.7, Windows 2000
> >
>
> Hi Andrei
>
> Because your query above has no join predicates, it's performing a
straight
> Cartesian product which means it will generate every combination of rows
> possible from *all* tables. However, as table C has no matching rows, it
has
> nothing to join itself with the other tables therefore there a no rows
that
> can be combined from all tables.
>
> Therefore no rows are returned.
>
> If you want to return some rows, regardless of the fact there are no rows
> from table C, you'll need to perform an outer join with C. I can't tell
what
> join predicates would be appropriate but you'll need to place a (+) on the
> table C join predicate (with 8.1.7).
>

When I say table C, I mean inline view C but you get my drift (I hope)

Cheers

Richard Received on Tue May 27 2003 - 08:29:20 CDT