[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:

SELECT *
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.

Edit:

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:

SELECT *
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.

Source: stackoverflow.com

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

Is there a shortcut for SELECT * FROM?

SQLite Update Syntax for string concatenation?

What can i use for a no-op in T-SQL?

Syntax error when running a MySQL CREATE TABLE statement

default as first option in switch statement?

Java For-Each Loop : Sort order

Does Python have an equivalent to 'switch'?

What is the meaning of '==' in C?

BNF vs EBNF vs ABNF: which to choose?

Location of parenthesis for auto-executing anonymous JavaScript functions?