Re: Difference between = and IN

From: Andy (Andy_at_discussions.microsoft.com)
Date: 02/07/05 

Date: Mon, 7 Feb 2005 02:21:01 -0800

Thanks Steve
So presumably if there is only one item it is more efficient
to use = as the query processor does not need to convert it whereas the IN
statement will be converted to = anyway?

The main reason I am interested is that I am writing code that will build an
SQL statement and wanted to know if I would be better off using an IN
operator all the time or checking for instances where an = would suffice and
using that instead.

Thanks
Andy

"Steve Kass" wrote:

> The better question might be what the "sameness" is between
> IN and =. They are not interchangeable at all, though they
> come close if the IN list contains only one item. You would
> lose nothing if only one of them were around, but they aren't
> synonyms for each other. To rewrite a IN (x,y,z), you need
> more than =; you need OR also.
>
> As far as the query processor goes, it treats a IN (x,y,z)
> just as if it were (a = x OR a = y OR = a = z)
>
> Steve Kass
> Drew University
>
> Andy wrote:
>
> >Hi
> >Does anybody know what the difference is between the 'IN' and '=' operators.
> > I am aware that '=' compares two expressions whereas 'IN' looks to see if an
> >expression appears in a list and that a statement including an '=' operator
> >is quicker to type than one with an 'IN'. Other than that I can't see that
> >'=' does anything that 'IN' can't do. Is there an overhead associated with
> >using the 'IN' operator?
> >Thanks in advance...
> >
> >
>