Literal value with "IN" clause

[John Smith]

Howdy,

Is it okay to use a literal value with the IN clause. E.g.

SELECT somefield, anotherfield
.....
WHERE ...etc.
AND 1234 IN (SELECT userid FROM tblUsers)

I was told it wasn't valid, but I'm pretty sure it worked for me. Just
seeking clarification.

cheers,

[Erland Sommarskog]

Re: Literal value with "IN&quo t; clause

John Smith (genericemailac count@genericdo main.genericTLD ) writes:[color=blue]
> Is it okay to use a literal value with the IN clause. E.g.
>
> SELECT somefield, anotherfield
> ....
> WHERE ...etc.
> AND 1234 IN (SELECT userid FROM tblUsers)
>
> I was told it wasn't valid, but I'm pretty sure it worked for me. Just
> seeking clarification.[/color]

That should be OK. A bit unusual maybe, but certainly valid.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: Literal value with "IN&quo t; clause

>> I was told it wasn't valid, but I'm pretty sure it worked for me. <<

It is valid, Standad SQL and can be a useful trick to avoid OR-ed
predicates. The IN() list just has to be expressions that will cast
to the proper data type.