CODEWITHSUNDEEP
mysqlsyntaxrange
Marty
Marty

Reputation: 2666

How to search for a value being in a known range in mySQL

Let's say I have a query like this:

SELECT * FROM table_name WHERE venue = "1" OR venue = "10"

That's great, works fine. But let's say I have a list of 50 venue numbers. Is there an easy way to do this where I don't have to write out 50 different ORs?

Thanks!

Upvotes: 0

Views: 780

Answers (5)

David R Tribble
David R Tribble

Reputation: 12204

Not sure if MySQL supports this...

Use the BETWEEN operator:

SELECT *
  FROM table
  WHERE venue BETWEEN '1' and '50';

Although you may need to be more explicit when dealing with character string datatypes:

SELECT *
  FROM table
  WHERE venue BETWEEN '1' and '9'
     OR venue BETWEEN '10' and 50';

Of course, BETWEEN won't help if your list of numbers are non-contiguous.

Upvotes: 0

EMPraptor
EMPraptor

Reputation: 642

Try...

SELECT * FROM table_name WHERE venue in ('1', '10')

I know it works in Oracle. "in" operator is probably part of standard SQL? Dunno.

edit: changed double to single quotes.

Upvotes: 0

Zoidberg
Zoidberg

Reputation: 10333

SELECT * FROM table_name WHERE venue IN ('1','10')

Best answer would be

SELECT * FROM table_name WHERE venue IN @myVar

Where @myVar is a list set in the query,

Upvotes: 1

Arthur Reutenauer
Arthur Reutenauer

Reputation: 2630

Use the IN keyword: ... WHERE venue IN (2, 3, 5, 7, 11, ...) ;

Upvotes: 0

ChssPly76
ChssPly76

Reputation: 100736

You can use "IN" instead:

SELECT * FROM table_name WHERE venue IN (1, 12, 23, ... 150)

Upvotes: 4

Related Questions