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?

开发者_Go百科

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.

---

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.

---

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

---

Performance = identical.

Readability = "IN"

---

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.

---

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