Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Problem by query with subquery

Re: Problem by query with subquery

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 27 May 2003 23:26:08 +1000
Message-ID: <SpJAa.44368$1s1.597763@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).

Cheers

Richard Received on Tue May 27 2003 - 08:26:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US