Skip to main content
Ben Nadel at CFinNC 2009 (Raleigh, North Carolina) with: Critter Gewlas
Ben Nadel at CFinNC 2009 (Raleigh, North Carolina) with: Critter Gewlas ( @Critter )

SQL IN Directive Much Faster Than Multiple OR Clauses

Published in , Comments (5)

I use the IN directive a lot in SQL, especially in conjunction with ValueList()'s of IDs. I was working on a query today that was dealing with thousands of IDs and I got curious as to how the IN directive compared to a more labor-intensive grouping or OR clauses. To me, the IN directive is elegant, but if it's slow, I wouldn't mind writing out the ORs.

To test this, I queried the page-hit IDs from a web statistics database and then used that list of IDs to basically re-query the database for the same records:

<!--- Query for the hit IDs. --->
<cfquery name="qIDs" datasource="..." username="..." password="...">
		web_stats_hit h

<!--- Query for the ID records using the IN directive. --->
<cfquery name="qInTest" datasource="..." username="..." password="...">
		web_stats_hit h
			In the ValueList(), I am passing in comments in an attempt
			to make the amount of SQL larger so that it compares to the
			next query which physically has more character data.
		---> IN ( #ValueList(, ", /*..*/" )# )

<!--- Query for the ID records using an OR clause for every ID. --->
<cfquery name="qOrTest" datasource="..." username="..." password="...">
		web_stats_hit h
		1 = 1
			Loop over IDs. I am doing a 'sloppy' loop here to create
			as little white space as possible so as to not have to push
			so much darn data to the SQL server.
		<cfloop query="qIDs">OR </cfloop>

The web stats database returns about 3,500 records. In the first query, I am using the IN directive and am just passing in a comma-delimited list of IDs. I am also throwing some bunk comments into the delimiter list so that the actually, physical size of the first query is comparable to that of the second. Value lists just take up less character data.

It turns out that the IN directive is MUCH faster. For the examples above, the IN test runs on average at just under 1,000 ms where as the OR test runs in just over 6,000 ms. If I take away the comments in the ValueList() call (ex. #ValueList( )#), then the IN test query drops to about 300 ms - a third the parsing and execution time.

So, it seems that raw character data accounts for a little bit of the speed of the query as the removing of the comments (/*..*/) demonstrates, but it looks like IN is just much faster than multiple ORs. This of course, only tests numeric data. I would have to come up with a slightly different test to see about string list data. Still, as I am using ID lists more often, this is very very interesting.

Want to use code from this post? Check out the license.

Reader Comments



The "1=1 OR" in your where cause will return ALL records and probably explains the large difference in the run times.

I'm sure the IN is faster than multiple OR's anyway, but probably not to the degree that your test showed.



Good catch! That was supposed to be "1 = 0". I probably just copied it over incorrectly as the tests came back with the same results.


I've been wondering it myself and want to find out about it.
But in my test, the Or clause is faster than the In clause.

The number of records is millions (all data in the table not the result set. the result set should be hundreds to thousands)

But there's only 2 condition. Not many.

I suppose that if the case is few, Or will be better, while if the case is many, In will be better.

But, i'm not in condition to prove it though.
I haven't test it with many cause myself.

PS: i'm not sure there's will be response, since it's already very old.


In most RDBMS, IN statements are rewritten at parse time as a list of OR predicates. So there should be no difference in performance.

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel