[ACCEPTED]-SQL where field in vs. where field = with multiple ors?-syntax

Accepted answer
Score: 21

It more readable, and more universally accepted 8 to do:

FROM Wherever
WHERE Greeting in ('hello', 'hi', 'hey')

All modern SQL servers optimize your 7 queries, so they're both likely to be changed 6 into the same code that runs on the server, so 5 performance differences will be negligible 4 or non-existent.


Apparently the in option is 3 faster, as it evaluates to a binary lookup, whereas 2 the multiple = just evaulates each statement 1 individually.

Score: 7

All major engines (MySQL, PostgreSQL, Oracle and SQL Server) will optimize 1 it to exactly same plans.

Score: 4

Performance = identical.

Readability = "IN"


Score: 0

I would say the first option involving in:

FROM Wherever
WHERE Greeting IN ('hello', 'hi', 'hey')

It 1 is:

  • much faster
  • easier to read
  • no need to use multiple or
  • less typing
  • easier to maintain in big queries
  • widely used

More Stuff:

SQL IN Directive Much Faster Than Multiple OR Clauses

Score: 0

The IN version is much clearer and, since 6 it's a single term, avoids the possibility 5 of missing or incorrectly structured parentheses 4 if you add other terms to the WHERE clause.

In 3 addition, I believe that more SQL implementations 2 will optimize the IN version assuming that 1 an index is available.

More Related questions