CODEWITHSUNDEEP
mysqlconditional-statementsbetween
user1954544
user1954544

Reputation: 1687

Mysql and between\in range condition

We have x2 columns min and max. Each can be null or integer. When we start search throw table we cannot use BETWEEN command... Question is, how to find in range with this conditions

  1. value is greater then min (if it's not null)

and

  1. value is less then max (if it's not null)

and

  1. value is in range of min and max (if they BOTH not null)

value - our integer number. As you can see we cannot use BETWEEN command.

Upvotes: 2

Views: 893

Answers (4)

user1954544
user1954544

Reputation: 1687

My solution Yii2 AR like

    $query
        ->joinWith(['vacancySalary'])
        ->andWhere([
            'and',
            'IF (vacancy_salary.min IS NULL, ' . $this->salaryMin . ', vacancy_salary.min) >= ' . $this->salaryMin,
            'IF (vacancy_salary.max IS NULL, ' . $this->salaryMin . ', vacancy_salary.max) <= ' . $this->salaryMin
        ]);

Simple answer is use IF condition and proper values.

ADDED: Another way to go

$query
    ->joinWith(['vacancySalary'])
    ->andWhere($this->salaryMin . ' BETWEEN IF(vacancy_salary.min IS NULL, 0, vacancy_salary.min) AND IF(vacancy_salary.max IS NULL, 0, vacancy_salary.max)');

Upvotes: 0

Richard Hamilton
Richard Hamilton

Reputation: 26444

Without the option of using BETWEEN, I would recommend using a simple WHERE-AND clause.

If null values are not allowed, you should use the COALESCE function

http://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

SELECT * 
FROM SCORES
WHERE score >= COALESCE(min_score, score)
AND score <= COALESCE(max_score, score)

Here is a sample fiddle I created

http://sqlfiddle.com/#!9/306947/2/0

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521589

Use this:

WHERE col BETWEEN COALESCE(min, -2147483648) AND COALESCE(max, 2147483647)

According to your logic, if either the min or max be NULL, then the restriction should be ignored. In the above WHERE clause, if min be NULL then col will always be greater than the lower boundary, assuming that col is an integer. Similar logic applies to the max condition.

The large (and small) numbers you see represent the largest and smallest possible values for an integer in MySQL.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94959

So NULL means no limit. You can still use BETWEEN:

select *
from mytable
where @value between coalesce(minvalue, @value) and coalesce(maxvalue, @value);

Or simply AND:

select *
from mytable
where @value >= coalesce(minvalue, @value) 
  and @value <= coalesce(maxvalue, @value);

Or the very basic AND and OR:

select *
from mytable
where (@value >= minvalue or minvalue is null)
  and (@value <= maxvalue or maxvalue is null);

Upvotes: 2

Related Questions