Re: Advanced SQL question: NOT EXISTS

:> eddas_at_huber.com (Don Smith) writes:
:>
:> In article <3q2c1i$fep_at_News1.mcs.com>, stowe_at_mcs.net writes:
:>
:> |>
:> |> 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
:>
:> Sorry, I can't buy into this argument. There's no reason why the NOT in NOT EXISTS
:> should have any effect but to reverse the result of the subquery. In either case, the
:> subquery should stop as soon as a match (ie, a row which satisfies the subquery criteria)
:> is found. Yes, it usually takes longer to find out that something does not exist than
:> to find out that it does. But it's the subquery, not the NOT, which will determine how
:> frequently the row sought will not be found. Not?
 

:> Donald A. Smith Just another of the many Don Smiths
:> eddas_at_huber.com on the net
>>>>

I agree with Donald Smith. Just wanted to mention the following:

The search for both exists or not exists operators may not have be a *full scans* if the search path is through index.

Ganesh Puranik,
Consultant, Surya Systems, Inc. Received on Sat May 27 1995 - 00:00:00 CEST