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?
Oracle White Papers: Fusion Middleware