2

In MySQL, I can specify an alternative match with OR:

SELECT * FROM table WHERE var = 1 OR var = 2

When there are many alternatives, listing them all like this will become cumbersome. Is there a way, where I can give a list, vector or array of values? Sort of like this pseudocode:

SELECT * FROM table WHERE var IN {1, 2, 5, 11, 45}
  • 5
    `SELECT * FROM table WHERE var IN (1, 2, 5, 11, 45)` – Ry- Aug 14 '16 at 09:33
  • @Ryan Thank you! I can't believe I almost made up the correct way :-) –  Aug 14 '16 at 09:36
  • 2
    For the person voting to close: No, this question was not caused by a typographical error. I made up the "solution" from my understanding of maths, where braces represent a set, and from R code, where "IN" is used to select from a vector. It is an accident that it resembled the solution. –  Aug 14 '16 at 09:42

2 Answers2

2

You can specify IN with parenthesis rather than braces. 

SELECT * FROM table WHERE var IN (1, 2, 5, 11, 45)
Darren
  • 68,902
  • 24
  • 138
  • 144
1

First equality/comparison operator in MySQL is = only not ==.

Yes you can achieve the same by introducing IN clause. But your syntax doesn't comply with MySQL standard. It should be: 

SELECT * FROM table WHERE your_column IN (1,2,5,11,45)

Note:

Use IN.

IN will use an index.

OR will (afaik) not use an index.

Also, and this point is not to be sneezed at, the IN version:

  • uses less code
  • is easier to maintain
  • is easier to understand

For those reasons alone I would be prepared suffer a little performance to gain code quality, but you actually gain performance too.

OR VS IN

Community
  • 1
  • 1
1000111
  • 13,169
  • 2
  • 28
  • 37
  • Thank you, @1000111, I skipped the `FROM table` part to make it easier to focus on the parts that I was asking about. –  Aug 14 '16 at 09:37
  • Also you can take a look at the performance comparison between OR and IN in mysql. Added a reference. @what – 1000111 Aug 14 '16 at 09:40
  • 1
    When I see an `OR` in a query that needs to be performant I cry a little. It's true. They're never good news and if you can't stomp them out you've got problems. – tadman Aug 14 '16 at 10:12
  • The link you posted is a few years old and simply claims *OR will (afaik) not use an index* and links to an even older post. MySQL is know for having a weak optimizer, but I wouldn't expect that recent versions show any difference for above example (`IN` is just a shortcut for multiple `OR`s). The only way to found out is comparing the actual plans. – dnoeth Aug 14 '16 at 10:18
  • @tadman: You probably cry a lot :-) `OR`ed WHERE-conditions are perfectly valid (and `IN` simply hides the `OR`), they are definitely bad in joins and might be bad when the optimizer drops index access. – dnoeth Aug 14 '16 at 10:21
  • I would be grateful if you provide any updated version of the above comparison @dnoeth – 1000111 Aug 14 '16 at 10:23
  • @dnoeth Valid and performant are two different things. An unavoidable `OR` can be very punishing on performance. They're usually a sign of a bad schema design or the programmer expecting way too much from the database. – tadman Aug 14 '16 at 10:28
  • I'm not a MsSQL guy, but here you are: http://www.tocker.ca/2015/05/25/optimizer-trace-and-explain-formatjson-in-5-7.html, this clearly shows that both are optmized exactly the same. – dnoeth Aug 14 '16 at 10:35