RE: [SQL] To create the query for a select where one is NOT in th e other

On Mon, 21 Sep 1998, Jackson, DeJuan wrote:

> Date: Mon, 21 Sep 1998 13:07:13 -0500
> From: "Jackson, DeJuan" <djackson@cpsgroup.com>
> To: Eric McKeown <ericm@palaver.net>, Karl Denninger <karl@denninger.net>
> Cc: pgsql-sql@postgreSQL.org
> Subject: RE: [SQL] To create the query for a select where one is NOT in th    e other
>
> > On Sat, 19 Sep 1998, Karl Denninger wrote:
> >
> > I think what you need to do is use a subquery:
> >
> > select key_field from table1 where key_field NOT IN (select key_field
> > from
> > table2) ;
> >
> This query will run faster and get you the same results:
> SELECT key_field FROM table1 a WHERE NOT EXISTS(SELECT b.key_field FROM
> table2 b WHERE b.key_field = a.key_field);

Bear with me; I'm learning, and I have a couple of questions.

>
> The reason query two is faster than one is two fold...
> 1) The second query's subquery will only return row's if and only if
> there is a match, unlike the first query which will have to return every
> row in table2 for every row in table1 and then compare table1.key_field
> to every one of those values.

I understand that--makes perfect sense.

> 2) The first query will not use indexes at all.  Where as the second one
> will for the subquery and a table scan for table1.
>
> I suggest timing both queries with and without indexes.  And use
> 'explain' to see the query plans.  Most IN/NOT IN queries can be written
> as an EXISTS/NOT EXISTS query and run faster.

So what is the main difference between IN/NOT IN and EXISTS/NOT EXISTS
queries?  The fact that IN/NOT IN doesn't use indexes and EXISTS/NOT
EXISTS does?  Is EXISTS/NOT EXISTS standard SQL syntax?

Thanks for the pointers...
_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net