May 27, 2012

Using multiple ='s for IN?

Question by David542

What would be the difference between doing:

SELECT person FROM population WHERE id = 1 or id = 2 or id = 3

and –

SELECT person FROM population WHERE id IN (1,2,3)

Are they executed the exact same way? What difference is there? Would there ever be a reason where one would you IN rather than multiple =‘s?

Answer by Starx

No, they perform the same thing. The IN minimizes the query string. That’s all. Such statements help in query optimization.

One difference in these two comparison operators would be that IN uses a SET of values to compare, unlike the “=” or “<>” which takes a single value.

According to the manual:

if expr is equal to any of the values in the IN list, else returns 0.
If all values are constants, they are evaluated according to the type
of expr and sorted. The search for the item then is done using a
binary search. This means IN is very quick if the IN value list
consists entirely of constants.

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!