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(at)cpsgroup(dot)com>
> To: Eric McKeown <ericm(at)palaver(dot)net>, Karl Denninger <karl(at)denninger(dot)net>
> Cc: pgsql-sql(at)postgreSQL(dot)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(at)palaver(dot)net
http://www.palaver.net