CODEWITHSUNDEEP
sqlsyntax
froadie
froadie

Reputation: 83053

SQL where field in vs. where field = with multiple ors?

Which of these is better to use in regard to performance? ...in regard to readability / understandability? ...in regard to accepted standards?

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

OR

SELECT *
FROM Wherever
WHERE Greeting = 'hello' 
   OR Greeting = 'hi'
   OR Greeting = 'hey'

The first seems more intuitive / clear to me, but I'm unsure of accepted standards and performance.

Upvotes: 17

Views: 20300

Answers (5)

Sarfraz
Sarfraz

Reputation: 382696

I would say the first option involving in:

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

It 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

Upvotes: 0

gbn
gbn

Reputation: 432261

Performance = identical.

Readability = "IN"

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425361

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

Upvotes: 6

Larry Lustig
Larry Lustig

Reputation: 50970

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

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

Upvotes: 0

Mike Trpcic
Mike Trpcic

Reputation: 25659

It more readable, and more universally accepted to do:

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

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

Edit:

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

Upvotes: 23

Related Questions