Re: IN vs NOT IN

From: Ezr Vinh <d_a_p_at_my-deja.com>
Date: Thu, 07 Dec 2000 18:59:35 GMT
Message-ID: <90ommj$rp6$1@nnrp1.deja.com>

In article <90ol2v$a75$1_at_porthos.nl.uu.net>, "Dick Doornbos" <dikkie-dik_at_mail.be> wrote:
>
> SELECT COUNT(*)
> FROM struct
> WHERE EXISTS
> (SELECT 1
> FROM detail
> WHERE status = 0
> );
>
> or
>
> SELECT COUNT(*)
> FROM struct
> WHERE NOT EXISTS
> (SELECT 1
> FROM detail
> WHERE status != 0
> );

I can't be 100% certain without actually trying it, but I don't think that will quite do it, because there is nothing in there to correlate the inner query with the outer, but the idea of using NOT EXISTS is a valid possibility; it might or might not work better than the other idea of using a MINUS that I suggested in a previous post.

Here's a modification of the NOT EXISTS usage which ought to work:

select count(*) from struct a
where not exists
(select 1 from detail
where objectid = a.object_id and

status != 0);

In this query, the inner select will at least be able to do an index range scan on the detail.object_id index.

-Dave

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 07 2000 - 12:59:35 CST