THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

From the I did not know this would work department

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?

Published Thursday, April 09, 2009 4:42 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Aaron Alton said:

Great article.  This is definitely the first time I've seen that syntax used!

April 9, 2009 4:06 PM
 

Claude said:

Sweet. I didn't know you could that!

I can't make sense of the "execution plans," but the first query would appera faster since the constant is on the left side of the evaluation...?

April 9, 2009 5:45 PM
 

James said:

That's an interesting use of IN certainly! Didn't know it could be used like that.

If you're interested, I have stopped using 'WHERE Field IN (SELECT Field FROM Table)' since, at 1am once, working on a project we were having difficulty releasing I discovered it had a bug. This was in SQL 2k and I have no idea whether the bug is still in SQL 2k5 or 8.

Basically it was excluding particular values, even though they were returned by the IN (SELECT) query. It's poor code anyway to use that so I stick with joins now. I can tell you, at 1am you tend to doubt yourself rather than SQL and it took a good 45 minutes with a colleague of trying to work out what on earth was going wrong with the stored procedure! :-)

April 10, 2009 3:09 AM
 

Mladen said:

yeah i always use that if i have that kind of condition. saw it a couple of years ago on sqlteam.com forums. i like it a lot.

April 10, 2009 10:54 AM
 

JohnC said:

I would not use it because some poor person after me would have to spend time figuring out why I did it that way and does it have any impact on performance.. I tried to write TSQL in a standard way so it is easy to understand for someone later..

April 10, 2009 12:19 PM
 

AaronBertrand said:

James, IN and NOT IN have definite issues when the column in the derived table allows NULLs.

Claude, would you also think that this:

WHERE 'bar' = foo

...is more efficient than this:

WHERE foo = 'bar'

Just because of whether the constant is on the left or the right?  The problem of sargable queries has nothing to do with geometry.  :-)

April 10, 2009 7:06 PM
 

jerryhung said:

+1 Did not know that either

Cool way of using IN for sure

April 13, 2009 3:37 PM
 

Peso said:

May 8, 2009 2:23 AM
 

Jennifer McCown said:

Interesting. And since IN is just a shorthand way of saying "this = 'value' OR this = 'another value' OR this = 'third value'", that means you can also do this:

select * from TableName

where 15 = FirstID OR 15 = SecondID

Same as:

select * from TableName

where 15 IN (FirstID, SecondID)  

Cool.  It is more readable to spell it out, though (using ORs).

September 14, 2009 10:19 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement