Re: Oracle EXISTS

From: Chuck <skilover_nospam_at_softhome.net>
Date: Mon, 06 Jun 2005 14:03:39 GMT
Message-ID: <1118066619.26df15bbd8637cf7315c9e2ef7795bd9@bubbanews>

cochrane68_at_hotmail.com wrote in
news:1118065874.206271.123930_at_g49g2000cwa.googlegroups.com:

> I'm having a little trouble understanding the EXISTS operator. Why
> do the following SQL statements return a different number of records:
>
> SELECT t.id, lt.id
> FROM table t, link_table lt
> WHERE lt.id(+) = t.id;
>
>
> SELECT t.id
> FROM table1 t
> WHERE
> EXISTS (
> SELECT 0
> FROM link_table1 lt
> WHERE lt.id(+) = t.id
> );
>
> I thought EXISTS basically allows you to join tables in subselect
> statements, but there appears to be some subtle differences I'm
> missing.
>
>

The (+) operator is an outter join operator. Your first query is returning everything in table that is NOT IN link_table.

In this case I think you are looking for a NOT EXISTS query.

select t.id
from table t
where NOT EXISTS (

    select 0
    from link_table lt
    where lt.id = t.id
); Received on Mon Jun 06 2005 - 09:03:39 CDT