EXISTS Vs. NOT EXISTS

Which query is faster? assuming condition1 results in table scan (say 10000
records).
IF EXISTS(condition1)
BEGIN
operation1
END
ELSE
BEGIN
operation2
END
IF NOT EXISTS(condition1)
BEGIN
operation2
END
ELSE
BEGIN
operation1
ENDAm Mon, 24 Apr 2006 08:55:42 -0400 schrieb Justin:

> Which query is faster? assuming condition1 results in table scan (say 1000
0
> records).
> IF EXISTS(condition1)
> BEGIN
> operation1
> END
> ELSE
> BEGIN
> operation2
> END
>
> IF NOT EXISTS(condition1)
> BEGIN
> operation2
> END
> ELSE
> BEGIN
> operation1
> END
There will be no difference.
For example: your record has a field called number, this field is set
randomly. Now you are looking for a record with number = 1000.
Let's say, this is the fifth record from top.
if exists(select * from table where number = 1000) can nothing say on
record 1, 2, 3 or 4, but record 5 has this number, so you get TRUE as
answer.
if not exists(select * from table where number = 1000) can nothing say on
record 1, 2, 3 or 4, but record 5 has this number, so you get FALSE as
answer.
bye, Helmut|||If it can't use an index and does a full table scan, it is going to search
the table until it finds a record that meets the criteria of your exists.
Once it finds a record, it knows two things:
Exists = True
Not Exists = False
If no record exists, it will search every row trying to find one that meets
the criteria. When it has searched the entire table and found nothing, it
knows two things:
Exists = False
Not Exists = True
Either way it has an answer to your question with the same number of
lookups. At this point the choice is one of coding style and readability.
Personally I try to avoid negative criteria and go with the positive, simple
because negatives require more thought to understand whats happening. First
you ask a question, then you reverse the answer. Easy for a computer,
tougher for humans.
Now, it might be worth trying both ways and checking the execution plans,
just in case the optimizer handles them different. Since they are logically
identical as far as how much work is required to get an answer, the
optimizer should treat them as such, but the optimizer never ceases to amaze
me in some of the decisions that it makes.
"Justin" <jyang@.ioutsource.info> wrote in message
news:u52Dj55ZGHA.4564@.TK2MSFTNGP03.phx.gbl...
> Which query is faster? assuming condition1 results in table scan (say
10000
> records).
> IF EXISTS(condition1)
> BEGIN
> operation1
> END
> ELSE
> BEGIN
> operation2
> END
>
> IF NOT EXISTS(condition1)
> BEGIN
> operation2
> END
> ELSE
> BEGIN
> operation1
> END
>|||On Mon, 24 Apr 2006 08:55:42 -0400, Justin wrote:

>Which query is faster? assuming condition1 results in table scan (say 10000
>records).
>IF EXISTS(condition1)
>BEGIN
> operation1
>END
>ELSE
>BEGIN
> operation2
>END
>
>IF NOT EXISTS(condition1)
>BEGIN
> operation2
>END
>ELSE
>BEGIN
> operation1
>END
>
Hi Justin,
No difference, as already explained by Helmut and Jim.
But depending on what exactly the condition and the operations are, it
might be possible to get a faster version by using a different (yet
equivalent) logic.
As an example - the following common construct:
IF EXISTS (SELECT ...) -- Search specific row
BEGIN
UPDATE ... -- Update of row just found
END
ELSE
BEGIN
INSERT ... -- Insert of row that was searched for
END
Can be rewritten as
UPDATE ... -- Update of (maybe nonexistant) row
IF @.@.ROWCOUNT = 0
BEGIN
INSERT ... -- Didn't exist - insert it
END
Or as (same idea; logic reversed)
INSERT ...
WHERE NOT EXISTS (SELECT ...)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE ...
END
Even if these examples don't match your requirement, you might be able
to find a parallel. Or, if you post more details, we can help you find
more efficient ways to get the job done. See www.aspfaq.com/5006 for the
information to provide in order to get the best replies.
Hugo Kornelis, SQL Server MVP