Q

IN list or series of OR conditions?

SQL expert Rudy Limeback explains the advantage of using the IN list in this situation.

Can you please tell me the advantage of using IN clause where the same can be done using the OR clause? For example:

select * from a 
where a.id in ('1','2','3')

or

select * from a 
where a.id='1'
or a.id='2'
or a.id='3'

Which one is advisable?

The IN list is better, because it is more compact. Readability of SQL is important, as you will learn when you go back to a complex query that you wrote some time ago and try to figure out what it's doing.

select * 
  from a 
 where a.id in ( 1,2,3 )

Please note that if your id column is numeric, you should not enclose the values in quotes, which makes them strings. Only MySQL performs the implicit conversion back to numeric; all other major database systems will give you a syntax error. Of course, if your id column is actually a character column, then you would want to compare it to strings... but then, why would the character values be numbers?

This was first published in June 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close