On Tue, 2005-06-28 at 17:10 -0400, Ann W. Harrison wrote:

> William L. Thomson Jr. wrote:
> >
> > Although I am curious as to the difference between WHERE NOT EXISTS and
> > WHERE <field> NOT IN? Is one better than the other, more efficient etc?
>
> Basically, IN always worries me because of it's tricky query
> transformation in the latter stages of compilation. If you start with
>
> select <fields> from table1 t1
> where not exists
> (select 1 from table2 t2
> where t1.field = t2.field)
>
> you've got a correlated subquery that will be optimized to walk (natural
> scan) table1 and perform indexed lookups on table2 -

Is that done on a per record bases "walk" as opposed to comparing entire
data set? Seems more of a singleton select as opposed to a regular
select statement? Using the 1? Not familiar with that syntax.

> assuming that
> table2.field is indexed, which, of course, it would be.

Yes, both tables are only 3 columns, all of which are foreign keys to
other tables in my case. Indexed either as part of the primary key or
foreign key.

> This query is more problematic...
>
> select <fields> from table1 t1
> where t1.field not in
> (select t2.field from table2 t2)
>

The ones I am using are more like

select <fields> from table1 t1
where t1.field not in
(select t2.field from table2 t2 where t1.field=t2.field)

Not sure if the sub-selects where statement makes any difference or not.
I have a feeling I am not going about the query in the most efficient
way?



--
Sincerely,
William L. Thomson Jr.
Support Group
Obsidian-Studios, Inc.
http://www.obsidian-studios.com