'SQL Server - IN clause with multiple fields

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.

sql-serversql-server-2008selectin-clause


Solution 1:[1]

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)

Solution 2:[2]

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...

..which means you start heading into perhaps something like:

--if your SQLS supports CONCAT
select * from user
where CONCAT(code, CHAR(9), userType) in ( select CONCAT(code, CHAR(9), userType) from ... )

--if no concat
select * from user
where COALESCE(code, 'no code') + CHAR(9) + userType in ( 
  select COALESCE(code, 'no code') + CHAR(9) + userType from ... 
)

CONCAT will do a string concatenation of most things, and won't zip the whole output to NULL if one element is NULL. If you don't have CONCAT then you'll string concat using + but anything that might be null will need a COALESCE/ISNULL around it.. And in either case you'll need something like CHAR(9) (a tab) between the fields to prevent them mixing.. The thing between the fields should be southing that is not naturally present in the data..

Tis a shame SQLS doesn't support this, that Oracle does:

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

but it's probably not worth switching DB for; I'd use EXISTS or a JOIN to achieve a multi column filter

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

Solution 3:[3]

How about this instead:

SELECT user.* FROM user JOIN userType on user.code = userType.code AND user.userType = userType.userType

Solution 4:[4]

You can either use joins 

SELECT * FROM user U 
INNER JOIN userType UT on U.code = UT.code 
AND U.userType = UT.userType

Solution 5:[5]

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

Solution 6:[6]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 cdhowie
Solution 2
Solution 3 Sam Holloway
Solution 4
Solution 5 Rick Savoy
Solution 6 abc def foo bar

Related Questions