CODEWITHSUNDEEP
mysqlsqlperformance
Mukeyii
Mukeyii

Reputation: 580

Optimize SQL with multiple BETWEENs in WHERE

I have a query like this:

SELECT [...]
FROM [...]
WHERE 
FirstInt BETWEEN 100 AND 200 AND
SecondInt BETWEEN 100 AND 200

Those variables have to be in the same range. Is there any way to write sth. like "(a || b) between 100 and 200"?

Upvotes: 2

Views: 75

Answers (2)

VBoka
VBoka

Reputation: 9083

I am not sure if this is what you were looking for:

SELECT [...]
FROM [...]
WHERE least(FirstInt , SecondInt, ... ) >= 100
AND greatest(FirstInt , SecondInt, ... ) <= 200

With this query you will find the smallest value of all of your columns(with function least) and compare it to the smallest value of your range. You will do the same with the largest value of all of your columns (with function greatest) and compare it to the largest value of your range.

Here is a small demo:

DEMO

With this query you can replace your multiple between statements or in other words you can use it when you want to copare multiple columns to the same range.

Upvotes: 2

Olgu Dulger
Olgu Dulger

Reputation: 60

I may not fully understand the question but i think you can use this.

SELECT [...]
FROM [...]
WHERE 
FirstInt >= 100 AND FirstInt<=200 OR
SecondInt >= 100 AND SecondInt <= 200

Upvotes: 0

Related Questions