Q

Is an IN-list faster than OR predicates?

Which queries are faster, queries using the IN operator, e.g.

WHERE clienStatus = "active" 
  AND (clientNo IN ("val1", "val2"))

or queries that use the OR operator, e.g.

WHERE clientStatus = "active"
  AND clientNo = "val1" 
   OR clientNo = "val2" 
  AND clientStatus = "active"

Execution speed would definitely depend on which database you're running, whether there are indexes on the tables, and how many values have to be tested.

That said, my money's on the IN-list, because an IN-list can be used by the database optimizer to construct a temporary index and thereby speed up execution, and also for the following two reasons:

  1. it's easier to code the IN-list correctly

  2. it's easier to code the ORs incorrectly

(Astute readers will see that these are the same reason.)

The example above for the OR conditions is a typical case where you have to stop and think about what's going on. It's not "maintenance-friendly" and you will surely make a mistake some day and leave the extra AND condition off or code it as an OR or something.

Restated with the implied parentheses that govern the sequence of evaluation of ANDs and ORs, the second query is --

WHERE ( clientStatus = "active" 
    AND clientNo = "val1" )
   OR ( clientNo = "val2" 
    AND clientStatus = "active" )

One of my habits, learned through much trial and even more error, is to code those parentheses explicitly, and to indent all SQL for readability. Thus, I would write it like this --

WHERE ( clientStatus = "active" AND clientNo = "val1" )
   OR ( clientStatus = "active" AND clientNo = "val2" )

Thus, it's easy to extend this to additional values --

WHERE ( clientStatus = "active" AND clientNo = "val1" )
   OR ( clientStatus = "active" AND clientNo = "val2" )
   OR ( clientStatus = "active" AND clientNo = "val3" )
   OR ( clientStatus = "active" AND clientNo = "val4" )
   OR ( clientStatus = "active" AND clientNo = "val5" )
   OR ( clientStatus = "active" AND clientNo = "val6" )

Here you can see that if the database does not optimize the fact that the same value of clientStatus is being tested in each compound condition, execution might slow down compared to the IN-list.

Additionally, it's a lot easier to extend the IN-list --

WHERE clientStatus = "active" 
  AND clientNo IN ("val1","val2","val3","val4","val5","val6" )

The last thing you want to do is write it like this --

WHERE clientStatus = "active"
  AND clientNo = "val1" OR clientNo = "val2" 
  AND clientStatus = "active" OR clientStatus = "active"
   OR clientNo = "val3"
   OR clientNo = "val4" AND clientStatus = "active"
   OR clientStatus = "active"
  AND clientNo = "val5"
   OR clientStatus = "active" AND clientNo = "val6"

because then when you make a coding mistake, as I have done here on purpose, it's a lot harder to figure out why it's not working.

For More Information

  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2002
This Content Component encountered an error

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close