SQL Server - IN clause with multiple fields

0 votes
asked Dec 15, 2010 by opensas

Is it possible to include in a IN clause multiple fields? Something like the following:

select * from user
where code, userType in ( select code, userType from userType )

I'm using ms sql server 2008


I know this can be achieved with joins and exists, I just wanted to know if it could just be done with the IN clause.

8 Answers

0 votes
answered Jan 15, 2010 by sam-holloway

How about this instead:

SELECT user.* FROM user JOIN userType on user.code = userType.code AND user.userType = userType.userType
0 votes
answered Jan 15, 2010 by abc-def-foo-bar

I don't think that query is quite portable,it would be safer to use something like

select * from user
where code in ( select code from userType ) and userType in (select userType from userType)
0 votes
answered Jan 15, 2010 by pavanred

You can either use joins

SELECT * FROM user U 
INNER JOIN userType UT on U.code = UT.code 
AND U.userType = UT.userType
0 votes
answered Dec 15, 2010 by oded

Not the way you have posted. You can only return a single field or type for IN to work.

From MSDN (IN):

test_expression [ NOT ] IN 
    ( subquery | expression [ ,...n ]
    ) 

subquery - Is a subquery that has a result set of one column. 
           This column must have the same data type as test_expression.

expression[ ,... n ] - Is a list of expressions to test for a match. 
                       All expressions must be of the same type as 
                       test_expression.

Instead of IN, you could use a JOIN using the two fields:

SELECT U.* 
FROM user U
  INNER JOIN userType UT
    ON U.code = UT.code
    AND U.userType = UT.userType
0 votes
answered Dec 15, 2010 by cdhowie

You could use a form like this:

select * from user u
where exists (select 1 from userType ut
              where u.code = ut.code
                and u.userType = ut.userType)
0 votes
answered Jan 31, 2013 by shd
select * from user
where (code, userType) in ( select code, userType from userType );
0 votes
answered Jan 13, 2015 by rick-savoy

I had to do something very similar but EXISTS didn't work in my situation. Here is what worked for me:

UPDATE tempFinalTbl
SET BillStatus = 'Non-Compliant'
WHERE ENTCustomerNo IN ( SELECT DISTINCT CustNmbr
             FROM tempDetailTbl dtl
            WHERE dtl.[Billing Status] = 'NEEDS FURTHER REVIEW'
              AND dtl.CustNmbr = ENTCustomerNo 
              AND dtl.[Service] = [Service]) 
  AND [Service] IN  ( SELECT DISTINCT [Service] 
             FROM tempDetailTbl dtl
            WHERE dtl.[Billing Status] = 'NEEDS FURTHER REVIEW'
              AND dtl.CustNmbr = ENTCustomerNo 
              AND dtl.[Service] = [Service]) 

EDIT: Now that I look, this is very close to @v1v3kn's answer

0 votes
answered Jan 26, 2015 by caius-jard

Only with something horrific, like

select * from user
where (code + userType) in ( select code + userType from userType )

Then you have to manage nulls and concatenating numbers rather than adding them, and casting, and a code of 12 and a usertype of 3 vs a code of 1 and a usertype of 23, and...

So there ya go: a solution that doesn't use joins or exists.. and a bunch of reasons why you shouldn't use it ;)

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...