Re: Advanced SQL question: NOT EXISTS

> vincen5158_at_aol.com (Vincen5158) writes:
> >>
> NOT EXISTS isn't a good choice -- for anything. It's very inefficient.
> (If you think about it, you'll
> realize that it requires that the ENTIRE table be checked for the value.)
> That being said, I'm not
> sure that NOT EXISTS is a good choice for what you're attempting to
> accomplish, either
> >>
> For the RULE based optimization I've found just the opposite, EXISTS is
> much quicker than IN. Its the IN clause that essentially has to create a
> complete result set (after all IN compares against a list) each time to
> satisify the query. EXISTS determines whether the condition is true and
> uses indexes to satisfy it. I'd be interested in your experiences on this.
>>>>

EXISTS is not the same as NOT EXISTS. If you consider it for a moment, EXISTS allows the query to stop running as soon as a match is found, but NOT EXISTS forces the entire table to be checked in order to rule out a match.

To summarize
EXISTS -> Good
NOT EXISTS -> Bad

Inicdentally, the optimization chosen doesn't matter a bit.

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Thu May 25 1995 - 00:00:00 CEST