I have used IN many times in my life, usually I would use hardcoded values. Something like these two queries
SELECT *
FROM
SomeTableWHERE
state in ('AZ','NY','NJ')
SELECT
*FROM
SomeTableWHERE
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.contactWHERE
'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.contactWHERE
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.contactWHERE
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?