SQL Server - IN clause with multiple fields

sql-server sql-server-2008 select in-clause
opensas picture opensas · Dec 15, 2010 · Viewed 62k times · Source

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.

Answer

Oded picture Oded · Dec 15, 2010

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

Related questions

Total Row Count in sql query---sql server 2008

My query is as follows BEGIN WITH MyCTE AS ( SELECT T.MusicAlbumTitle ,D.musicTitle ,D.mVideoID ,D.musicFileName ,T.ReleaseDate AS ReleasedDate ,D.MusicLength ,D.musicSinger ,D.MusicVideoID ,D.ExternalLink ,D.CoverImg ,ROW_NUMBER() OVER ( PARTITION BY D.MusicVideoID ORDER …

Read More
Find all tables whose name ends with a certain suffix

I have thousand of tables in database. Some names end with _History. For example : abc_History bcd_History 123_History How do I find all tables which name is end with _History. Some thing like: SELECT table_name FROM sys.tables …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More