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;

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);

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

SELECT *
FROM suppliers
WHERE supplier_id < 2100
OR supplier_id > 2499;

Posts to Review:

mysql get date interval of 30 days mysql concat function How MySQL Optimizes WHERE Clauses General Security Guidelines – MySQL Security