Re: IN vs NOT IN

From: Dick Doornbos <dikkie-dik_at_mail.be>
Date: Fri, 8 Dec 2000 00:33:23 +0100
Message-ID: <90p6n6$jp1$1@porthos.nl.uu.net>

Sorry, I forgot to correlate. Thanks for the correction

Dick

"Ezr Vinh" <d_a_p_at_my-deja.com> schreef in bericht news:90ommj$rp6$1_at_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 - 17:33:23 CST