From the I did not know this would work department

Denis Gobo — Thu, 09 Apr 2009 19:42:00 GMT

I have used IN many times in my life, usually I would use hardcoded values. Something like these two queries

SELECT *

FROM SomeTable

WHERE state in ('AZ','NY','NJ')

SELECT *

FROM SomeTable

WHERE value in (1,2,3,4,5)

Today I saw a query on a forum where a person used column names, this got me interested and I fired up this query in the adventureworks database

SELECT * FROM person.contact

WHERE 'thomas' in(Firstname,LastName,LEFT(emailaddress,7))

That query will return all the rows where Lastname or Firstname or the first 7 characters of the emaill address have the value Thomas

Here is what the execution plan looks like

|--Clustered Index Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
WHERE:(N'thomas'=substring([AdventureWorks].[Person].[Contact].[EmailAddress],(1),(7))
OR N'thomas'=[AdventureWorks].[Person].[Contact].[LastName]
OR N'thomas'=[AdventureWorks].[Person].[Contact].[FirstName]))

The way I usually write a query like that is like this

SELECT * FROM person.contact

WHERE Firstname ='thomas'

OR lastname ='thomas'

OR left(emailaddress,7) ='thomas'

Here is the plan for that

|--Clustered Index Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
WHERE:([AdventureWorks].[Person].[Contact].[FirstName]=N'thomas'
OR [AdventureWorks].[Person].[Contact].[LastName]=N'thomas'
OR substring([AdventureWorks].[Person].[Contact].[EmailAddress],(1),(7))=N'thomas'))

Or better yet like this

SELECT * FROM person.contact

WHERE Firstname ='thomas'

OR lastname ='thomas'

OR emailaddress LIKE 'thomas%'

The plan for that query is below

|--Clustered Index Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
WHERE:([AdventureWorks].[Person].[Contact].[FirstName]=N'thomas'
OR [AdventureWorks].[Person].[Contact].[LastName]=N'thomas'
OR [AdventureWorks].[Person].[Contact].[EmailAddress] like N'thomas%'))

So my question to you is do you ever write a query with columns inside IN?

Denis Gobo : Interesting

From the I did not know this would work department