[dba-SQLServer] IN() or NOT IN()

This is most certainly the case. IN() accepts only these arguments: a list
of one or more items, comma-delimited if more than one); a SELECT statement
that returns exactly one column. That's it.

Note that I avoided, as did Francisco, assuming that the sub-query is based
on table. It could be a UDF.

But those are your choices: a SELECT statement that returns one column, or a
comma-delimited list. The list might contain only one item, in which case
the comma is not required, but why would anyone use IN() for a one-item
list?

AFAIK you can only use SQL Server's decidedly limited implementation of
regex expressions in a LIKE clause, not an IN clause.

Arthur

On 5/16/07, Francisco Tapia <fhtapia at gmail.com> wrote:
>
> It definatly (in sql 2000) only takes list or single column subqueries.
> I'm not at my pc right now or I'd try that query on ss2005
>
> On 5/15/07, Stuart McLachlan <stuart at lexacorp.com.pg> wrote:
> > Are you sure? I've never seen anything that says that IN() takes
> > anything other than a Subquery or a List of expressions.
> >
> > The SQL Server 2000 that I have here returns:
> > "Invalid column name 4-9" if I try that.
> >
> > On 15 May 2007 at 19:44, kens.programming wrote:
> >
> > > You shouldn't have to use a comma limited list, just square brackets
> to
> > > designate your sets.
> > >
> > > IN ([4-9], [A-M])
> > > IN ([^1-3], [^N-T])
> > > NOT IN ([1-3], [N-T])
> > > NOT IN ([^4-9], [^A-M])
> > >
> > > Ken
> > >
> > > -----Original Message-----
> > > From: dba-sqlserver-bounces at databaseadvisors.com
> > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
> jwcolby
> > > Sent: Tuesday, May 15, 2007 7:34 PM
> > > To: dba-sqlserver at databaseadvisors.com
> > > Subject: [dba-SQLServer] IN() or NOT IN()
> > >
> > > I am trying to process a query where an income field has a set of
> possible
> > > values, 1-9 and A-T.  The client wants values 409 and A-M.  Logically
> that
> > > would be more efficient if it was NOT in(1-3,n-t).  Is it in fact more
> > > efficient?  And can ranges like that be specified or do I need to use
> > comma
> > > delimted lists 1,2,3,n,o,p...?
> > >
> > > John W. Colby
> > > Colby Consulting
> > > www.ColbyConsulting.com
> > >
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> > > No virus found in this incoming message.
> > > Checked by AVG Free Edition.
> > > Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date:
> 5/15/2007
> > > 10:47 AM
> > >
> > >
> > > No virus found in this outgoing message.
> > > Checked by AVG Free Edition.
> > > Version: 7.5.467 / Virus Database: 269.7.1/805 - Release Date:
> 5/15/2007
> > > 10:47 AM
> > >
> > >
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> --
> -Francisco
> http://sqlthis.blogspot.com | Tsql and More...
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>