Re: Oracle EXISTS

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Mon, 06 Jun 2005 13:21:48 -0400
Message-ID: <MC%oe.1895$II.177@news.itd.umich.edu>

cochrane68_at_hotmail.com wrote:

> 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.

Why should they return the same number of records? They're different queries, asking for different results. A corrolated sub query is not the same as a join (although it can be in some circumstances). And the outer join doesn't work the same way within a subquery as it does in a straight (outer) join.

To put it simply, the first query will pick up duplicates and nulls. (i.e. if an id in t appears multiple times in lt, you'll get multiple rows in the result set. And if an id in t does not appear in lt, you'll get one record)

The second query will not pick up duplicates or nulls. (i.e. if an id in t appears multiple times in lt, you'll only get one row in the result set. And if an id in t does not appear in lt, you won't get get it)

For instance :

////////////////////////////////////////////////

demonstrating duplicates :

SQL> CREATE TABLE t

   2 (
   3 id VARCHAR2(1),
   4 text VARCHAR2(8)
   5 );
Table created.

SQL> CREATE TABLE lt

   2 (
   3 id VARCHAR2(1),
   4 descr VARCHAR2(8)
   5 );
Table created.

SQL> insert into t values ('1','foo');
1 row created.
SQL> insert into t values ('1', 'bar');
1 row created.
SQL> insert into lt values ('1', 'FOO'); 1 row created.
SQL> insert into lt values ('1', 'BAR'); 1 row created.
SQL> commit;
Commit complete.

SQL> select * from t;
I TEXT
- --------
1 foo
1 bar

SQL> select * from lt;
I DESCR
- --------
1 FOO
1 BAR SQL> SELECT t.id, lt.id

   2 FROM t, lt
   3 WHERE lt.id(+) = t.id;

I I
- -
1 1
1 1
1 1
1 1

SQL> SELECT t.id

   2 FROM t
   3 WHERE

   4     EXISTS (
   5        SELECT 0
   6        FROM  lt
   7        WHERE lt.id(+) = t.id
   8     );

I
-
1
1

/////////////////////////////////////////////////

Demonstrating null behavior:

/* truncate and reload data in t and lt */

SQL> select * from t;
I TEXT
- --------
1 foo
1 bar
2 foo2
3 foo3

SQL> select * from lt;
I DESCR
- --------
1 DESC1
2 DESC2 SQL> SELECT t.id, lt.id

   2 FROM t, lt
   3 WHERE lt.id(+) = t.id;

I I
- -
1 1
1 1
2 2
3

SQL> select * from t where exists (select 0 from lt where lt.id(+) =t.id);

I TEXT
- --------
1 foo
1 bar
2 foo2

//////////////////////////////////////////////

//Walt Received on Mon Jun 06 2005 - 12:21:48 CDT