Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Problem by query with subquery
"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