[ACCEPTED]-SQL where field in vs. where field = with multiple ors?-syntax
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.
in option is 3 faster, as it evaluates to a binary lookup, whereas 2 the multiple
= just evaulates each statement 1 individually.
All major engines (
SQL Server) will optimize 1 it to exactly same plans.
Performance = identical.
Readability = "IN"
I would say the first option involving
SELECT * FROM Wherever WHERE Greeting IN ('hello', 'hi', 'hey')
It 1 is:
- much faster
- easier to read
- no need to use multiple
- less typing
- easier to maintain in big queries
- widely used
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