Re: Difference between = and IN

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/07/05 

Date: Mon, 07 Feb 2005 12:38:15 +0100

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)

Relevant Pages

  • Re: Using Execute SQL Task to insert rows
    ... I still can't see why this error message is relevant to my SQL. ... > www.SQLDTS.com - The site for all your DTS needs. ... >> Subquery returned more than 1 value. ... >> Here's my SQL statement: ...
    (microsoft.public.sqlserver.dts)
  • Re: Another sequential query field problem... are you out the Duan
    ... You seem to have seen some solutions that involve a subquery. ... attempted in terms of your SQL statement and results? ... >> Duane Hookom ... >>> What I need to do is creat a field in my query that creates a sequence ...
    (microsoft.public.access.queries)
  • Re: How to convert an integer to a string
    ... the sql statement executes without problems. ... concatenates in the way intended. ... This is the reason why I think it is necessary to convert the subquery ... have not been able to find this function on the access help file either. ...
    (comp.databases.ms-access)
  • Re: Nasty Query Here
    ... You are using a UNION between the two halves of the SQL statement, ... You are using TRUNCin the subquery, ... email returned from the inline view is null or not. ...
    (comp.databases.oracle.misc)
  • Re: Ranking Dates
    ... Which way to ranking goes doesn't matter. ... Here is my SQL Statement. ... This subquery is returning the count of records for which the invoice date is ...
    (microsoft.public.access.gettingstarted)