Mike Wazowski
10/7/2008 2:17:00 PM
Thanks Aaron.
It was my thoughts too that EXISTS does not need to retrieve or return
actual data, so would perform better.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:C510D82A.13E59%ten.xoc@dnartreb.noraa...
>> You will use IN when the SubQuery returns more than
>> one result.
>
> Do you mean compared to EXISTS, or compared to = ? You can also use
> EXISTS
> regardless of the number of results returned by the subquery.
>
> For the OP, I prefer EXISTS for a couple of reasons. One is the reason
> that
> Uri pointed out; if your subquery can return NULLs, then IN/NOT IN will
> not
> necessarily produce correct results. The other is that EXISTS has the
> possibility of performing better; it can stop "looking" once it has found
> its first result, and you can also set it to return a constant (e.g.
> SELECT
> 1) instead of a column... This makes it easier to spot as a
> non-data-returning element in your query.
>
> A
>