Difference between = and IN

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...
If I am not wrong,
Index will be used which are part of =, if any exist
Thanks,
RK
"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...
|||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...
>
|||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:
>
|||On Mon, 7 Feb 2005 02:21:01 -0800, Andy wrote:

>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
Hi Andy,
If you actually have to perform the subquery and count the number of rows
returned to choose between IN and =, then it's best to use IN, so that the
subquery is executed only once. If you have more efficient ways to
determine the number of rows returned, use =.
BTW, you might also consider using EXISTS instead of IN - it is generally
more efficient, but more important: NOT IN has very unpleasant behaviour
with NULLS and should be avoided (replace by NOT EXISTS); for uniformity,
I generally prefer to replace IN woth EXISTS as well. I use IN and NOT IN
only with a delimited list of values, never with a subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks for this - I wasn't aware there was an issue with IN/NOT IN and
Nulls! I was under the impression that Nulls would just be excluded from the
results. In this particular instance it will always be a single value or
delimited list. However, the app also uses a sub query so I will certainly be
having a look at that as well.
Thanks
Andy
"Hugo Kornelis" wrote:

> On Mon, 7 Feb 2005 02:21:01 -0800, Andy wrote:
>
> Hi Andy,
> If you actually have to perform the subquery and count the number of rows
> returned to choose between IN and =, then it's best to use IN, so that the
> subquery is executed only once. If you have more efficient ways to
> determine the number of rows returned, use =.
> BTW, you might also consider using EXISTS instead of IN - it is generally
> more efficient, but more important: NOT IN has very unpleasant behaviour
> with NULLS and should be avoided (replace by NOT EXISTS); for uniformity,
> I generally prefer to replace IN woth EXISTS as well. I use IN and NOT IN
> only with a delimited list of values, never with a subquery.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@.supernews.com
Lines: 31
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!border2.nntp.dca.giganews.com!nntp.gigan ews.com!wns13feed!worldnet.att.net!63.223.20.72!sj c1.usenetserver.com!news.usenetserver.com!sn-xit-03!sn-xit-08!sn-post-01!supernews.com!corp.superne
ws.com!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8209
On Mon, 7 Feb 2005 06:17:04 -0800, Andy wrote:

>Hi Hugo
>Thanks for this - I wasn't aware there was an issue with IN/NOT IN and
>Nulls! I was under the impression that Nulls would just be excluded from the
>results.
Hi Andy,
That's what most people think - but it's not true.
WHERE Column1 NOT IN (1, 2, NULL)
will (as per the ANSI-standard SQL-92) be equivalent to
WHERE Column1 <> 1
AND Column1 <> 2
AND Column1 <> NULL
And since Column1 <> NULL is neither "true" nor "false", but "unknown",
this expression can only evaulate to "false" (as a result of "false" AND
"unknown") or "unknown" (as a result of "true" and "unknown").
Consider it like this - would you be willing to bet a significant amount
that the 40 is NOT IN (your age, George W. Bush's age, my age)?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
I misunderstood what you meant in the previous post. I have worked out in
the past that you have to use 'IS [NOT] NULL' rather than '<> Null' (or 'IN
Null' or '= Null'). I thought you meant that NOT IN (1,2) would return
strange results if Nulls were present in the records whereas (I believe) NOT
IN (1,2) would return all rows that did not contain a 1, 2 or Null.
Its good to know why NOT IN (1,2,NULL) doesn't work though - I should
probably try reading the ANSI standard...
Thanks very much
Andy
"Hugo Kornelis" wrote:

> On Mon, 7 Feb 2005 06:17:04 -0800, Andy wrote:
>
> Hi Andy,
> That's what most people think - but it's not true.
> WHERE Column1 NOT IN (1, 2, NULL)
> will (as per the ANSI-standard SQL-92) be equivalent to
> WHERE Column1 <> 1
> AND Column1 <> 2
> AND Column1 <> NULL
> And since Column1 <> NULL is neither "true" nor "false", but "unknown",
> this expression can only evaulate to "false" (as a result of "false" AND
> "unknown") or "unknown" (as a result of "true" and "unknown").
> Consider it like this - would you be willing to bet a significant amount
> that the 40 is NOT IN (your age, George W. Bush's age, my age)?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 7 Feb 2005 07:19:02 -0800, Andy wrote:

>Hi Hugo
>I misunderstood what you meant in the previous post. I have worked out in
>the past that you have to use 'IS [NOT] NULL' rather than '<> Null' (or 'IN
>Null' or '= Null'). I thought you meant that NOT IN (1,2) would return
>strange results if Nulls were present in the records whereas (I believe) NOT
>IN (1,2) would return all rows that did not contain a 1, 2 or Null.
Hi Andy,
That's correct. It's good to see that you know that rows containing NULL
will not be returned be NOT IN (1, 2) - this is also a hefty surprise for
many people!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
That's a relief I didn't fancy having to re-check all my databases!!! Its
also nice to know I'm not a complete eejit!
Thanks for your help
Andy
"Hugo Kornelis" wrote:

> On Mon, 7 Feb 2005 07:19:02 -0800, Andy wrote:
>
> Hi Andy,
> That's correct. It's good to see that you know that rows containing NULL
> will not be returned be NOT IN (1, 2) - this is also a hefty surprise for
> many people!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>