Re: Oracle EXISTS

<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