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: Oracle EXISTS

Re: Oracle EXISTS

From: <fitzjarrell_at_cox.net>
Date: 7 Jun 2005 05:38:59 -0700
Message-ID: <1118147939.821674.131870@g43g2000cwa.googlegroups.com>

Jonathan Lewis wrote:
> <fitzjarrell_at_cox.net> wrote in message
> news:1118071653.744219.299260_at_g47g2000cwa.googlegroups.com...
> >
>
> > SQL> SELECT t.id, lt.id
> > 2 from t, link_table lt
> > 3 WHERE lt.id(+) = t.id;
> >
> > ID ID
> > ---------- ----------
> > 2 2
> > 4 4
> > 1000 rows selected.
> >
> > The second query returns the following results:
> >
> > SQL> SELECT t.id
> > 2 FROM t
> > 3 WHERE
> > 4 EXISTS (
> > 5 SELECT 0
> > 6 FROM link_table lt
> > 7 WHERE lt.id(+) = t.id
> > 8 );
> >
> > ID
> > ----------
> > 652
> > 654
> > 500 rows selected.
> >
> > The same result set is returned using an IN condition:
> >
> > SQL> SELECT t.id
> > 2 FROM t
> > 3 WHERE
> > 4 t.id in (
> > 5 SELECT lt.id
> > 6 FROM link_table lt
> > 7 WHERE lt.id(+) = t.id
> > 8 );
> >
> > ID
> > ----------
> > 652
> > 654
> >
> > 500 rows selected.
> >
> > SQL> spool off
> >
> > This indicates, to me, that the EXISTS query cannot return values from
> > one table where NULLs exist in the subquery. Notice all of the odd
> > valued results are missing, the results which return NULL from
> > link_table with an outer join. And this is where your confusion lies,
> > I believe.
> >
> > Given the queries you've supplied you'll never get the first result set
> > (that from the straight outer join) from an EXISTS query, even with the
> > outer join in the subquery.
> >
> >
> > David Fitzjarrell
> >
>
> I think the simpler interpretation may by found
> by looking at the query:
> select * from t1 where t1.id(+) = {non-existent value}
>
> This returns no rows, not a null row.
> I assume that this is required by the SQL standard,
> rather than being an Oracle implementation feature.
>
> Given that this query returns no rows, then:
> select from t2
> where exists (
> select * from t1 where t1.id(+) = {t2.id}
> )
>
> has to return no row when t2.id does not
> exist in t1 to be consistent with the simpler
> case.
>
> The IN subquery version yields to the same
> argument when there is no matching t1.id
> for a given t2.id then
>
> select from t2
> where t2.id in (
> select * from t1 where t1.id(+) = {t2.id}
> )
>
> becomes
>
> select from t2
> where t2.id in ({empty set})
>
> One thing you will notice about execution paths
> for the subquery versions: with the outer join
> constructs, they MUST execute as filters, Oracle
> cannot unnest the subqueries, or turn them into
> semi-joins. Without the outer join, 9i will turn
> either form subquery into a semi-join.
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Public Appearances - schedule updated April 5th 2005

Thank you, Jonathan, I missed that completely when building my example.  I considered the outer joins the same and they are not, and I can see where my logic was in error (I kept 'seeing' the output from the original outer join in my mind).

It's always good to have more than a single pair of eyes on a problem or possible solution. "Tunnel vision" can create erroneous advice; thank you, again, for pointing out something which should have been obvious.

David Fitzjarrell Received on Tue Jun 07 2005 - 07:38:59 CDT

Original text of this message

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