comparison operator between in mysql

comparison operator between in mysql

General Syntax – mostly used

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

expr BETWEEN min AND max

  • If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1,
    otherwise it returns 0.
  • This is equivalent to the expression (min <= expr AND expr <= max)
  • if all the arguments are of the same type. Otherwise type conversion takes place.

Exapmle:

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
-> 1, 0
mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT ‘b’ BETWEEN ‘a’ AND ‘c’;
-> 1
mysql> SELECT 2 BETWEEN 2 AND ‘3’;
-> 1
mysql> SELECT 2 BETWEEN 2 AND ‘x-3’;
-> 0

  • For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the
    values to the desired data type.
  • Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values.
  • If you use a string constant such as ‘2001-1-1’ in a comparison to a DATE, cast the string to a DATE.

 expr NOT BETWEEN min AND max

This is the same as NOT (expr BETWEEN min AND max).


expression BETWEEN value1 AND value2;

 

Example – With Numeric

Let’s look at some MySQL BETWEEN condition examples using numeric values. The following numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT *
FROM contacts
WHERE contact_id BETWEEN 50 AND 300;

This MySQL BETWEEN example would return all rows from the contacts table where the contact_id is between 100 and 200 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM contacts
WHERE contact_id >= 50
AND contact_id <= 300;

mysql between example
mysql between example

Example – With Date

Next, let’s look at how you would use the MySQL BETWEEN condition with Dates. When using the BETWEEN condition in MySQL with dates, be sure to use the CAST function to explicitly convert the values to dates.

The following date example uses the BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM order_details
WHERE order_date BETWEEN CAST('2018-12-01' AS DATE) AND CAST('2018-12-27' AS DATE);

This MySQL BETWEEN condition example would return all records from the order_details table where the order_date is between Dec 1, 2018 and Dec 27, 2018 (inclusive). It would be equivalent to the following SELECT statement:

SELECT *
FROM order_details
WHERE order_date >= CAST('2018-12-01' AS DATE)
AND order_date <= CAST('2018-12-27' AS DATE);

Example – Using NOT Operator

The MySQL BETWEEN condition can also be combined with the NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.

For example:

SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 2100 AND 2499;

This MySQL BETWEEN example would return all rows from the suppliers table where the supplier_id was NOT between 2100 and 2499, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_id < 2100
OR supplier_id > 2499;
mysql not between example
mysql not between example