A2-02-08.DML-MySQL BETWEEN Operator Explained
转载自：http://www.mysqltutorial.org/mysql-between
MySQL BETWEEN Operator Explained
Summary: in this tutorial, you will learn how to use MySQL BETWEEN operator to determine whether a value is in a range of values.
Introduction to MySQL BETWEEN Operator
The BETWEEN operator allows you to specify a range to test. We often use the BETWEEN operator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements.
The following illustrates the syntax of the BETWEEN operator:
|
1
|
expr [NOT] BETWEEN begin_expr AND end_expr;
|
The expr is the expression to test in the range that is defined by begin_expr and end_expr.
All three expressions: expr, begin_expr, and end_expr must have the same data type.
The BETWEEN operator returns true if the value of the expr is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of the end_expr otherwise it returns zero.
The NOT BETWEEN returns true if the value of expr is less than (<) the value of the begin_expr or greater than the value of the value of end_expr otherwise it returns 0.
If any expression is NULL, the BETWEEN operator returns a NULL value.
In case you want to specify an exclusive range, you use the greater than (>) and less than (<) operators.
MySQL BETWEEN examples
Let’s practice with some examples of using the BETWEEN operator.
MySQL BETWEEN with number examples
See the following products table in the sample database:
Suppose you want to find products whose buy prices are within the ranges of $90 and $100, you can use the BETWEEN operator as the following query:
|
1
2
3
4
5
6
|
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice BETWEEN 90 AND 100;
|
You can achieve the same result by using the greater than or equal (>=) and less than or equal ( <= ) operators as the following query:
|
1
2
3
4
5
6
|
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice >= 90 AND buyPrice <= 100;
|
To find the product whose buy price is not between $20 and $100, you combine the BETWEEN operator with the NOT operator as follows:
|
1
2
3
4
5
6
|
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice NOT BETWEEN 20 AND 100;
|
You can rewrite the query above using the less than (>), greater than (>), and logical operators (AND) as the following query.
|
1
2
3
4
5
6
|
SELECT
productCode, productName, buyPrice
FROM
products
WHERE
buyPrice < 20 OR buyPrice > 100;
|
MySQL BETWEEN with dates example
When you use the BETWEEN operator with date values, to get the best result, you should use the type cast to explicitly convert the type of column or expression to the DATE type.
For example, to get the orders whose required dates are from 01/01/2003 to 01/31/2003, you use the following query:
|
1
2
3
4
5
6
7
|
SELECT orderNumber,
requiredDate,
status
FROM orders
WHERE requireddate
BETWEEN CAST('2003-01-01' AS DATE)
AND CAST('2003-01-31' AS DATE);
|
Because the data type of the required date column is DATE so we used the cast operator to convert the literal strings ‘2003-01-01 ‘ and ‘2003-12-31 ‘ to the DATE data type.
In this tutorial, you have learned how to use the BETWEEN operator to test if a value falls within a range of values.
