Summary: in this tutorial, you will learn how to use MySQL BETWEEN operator to specify a range to test.
Introduction to MySQL BETWEEN Operator
The BETWEEN operator allows you to specify a range to test. The following illustrates the syntax of the BETWEENoperator:
expr (NOT) BETWEEN begin_expr AND end_expr
In the expression above:
All expressions:
expr,begin_exprandend_exprmust return values with the same data type.The
BETWEENoperator returns 1 if the value of theexpris greater than or equal to (>=) the value ofbegin_exprand less than or equal to (<= ) the value ofend_expr, otherwise it returns 0.The
NOT BETWEENreturns 1 if the value ofexpris less than (<) the value ofbegin_expror greater than the value ofend_expr, otherwise it returns 0.If any expression above is
NULL, theBETWEENreturnsNULL.
The BETWEEN operator is typically used in the WHERE clause of SELECT, INSERT, UPDATE and DELETE statements.
MySQL BETWEEN examples
Let’s practice with some examples of using the BETWEEN operator.
MySQL BETWEEN with number examples
Suppose you want to find product whose buy price within the range of $90 and $100, you can use the BETWEENoperator as the following query:
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:
SELECT productCode, productName, buyPrice FROM products WHERE buyPrice >= 90 AND buyPrice <= 100
To find the product whose buy price is out of the range of $20 and $100, you use combine the BETWEEN operator with the NOT operator as follows:
SELECT productCode, productName, buyPrice FROM products WHERE buyPrice NOT BETWEEN 20 AND 100
The query above is equivalent to the following query that uses the comparison operators, greater than operator ( >) and less than operator ( <) and a logical operator OR.
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 CAST function to explicitly convert the type of column or expression to the DATE type. For example, to get the orders whose required date is from 01/01/2003 to 01/31/2003, you use the following query:
SELECT orderNumber, requiredDate, status FROM orders WHERE requireddate BETWEEN CAST('2003-01-01' AS DATE) AND CAST('2003-01-31' AS DATE)
In the query above, because the data type of the required date column is DATE so we used the CAST function 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. You also learn how to combine the BETWEEN operator with the NOT operator to select data whose value that are not in a range of values.
随时随地看视频
