{"id":13730,"date":"2023-12-29T00:24:14","date_gmt":"2023-12-29T07:24:14","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13730"},"modified":"2023-12-29T00:24:15","modified_gmt":"2023-12-29T07:24:15","slug":"mysql-between","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-between\/","title":{"rendered":"MySQL BETWEEN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL BETWEEN<\/code> operator to determine whether a value is in a range of values.<\/p>\n\n\n\n

Introduction to MySQL BETWEEN Operator<\/h2>\n\n\n\n

The BETWEEN<\/code> operator is a logical operator that specifies whether a value is in a range or not. Here’s the syntax of the BETWEEN<\/code> operator:<\/p>\n\n\n

value BETWEEN low AND high;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The BETWEEN<\/code> operator returns 1 if:<\/p>\n\n\n
value >= low AND value <= high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Otherwise, it returns 0.<\/p>\n\n\n\n
If the value<\/code>, low<\/code>, or high<\/code> is NULL<\/a><\/code>, the BETWEEN<\/code> operator returns NULL<\/code> .<\/p>\n\n\n\n
For example, the following statement returns 1 because 15 is between 10 and 20:<\/p>\n\n\n
SELECT<\/span> 15<\/span> BETWEEN<\/span> 10<\/span> AND<\/span> 20<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example returns 0 because 15 is not between 20 and 30:<\/p>\n\n\n
SELECT<\/span> 15<\/span> BETWEEN<\/span> 20<\/span> AND<\/span> 30<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that MySQL treats 1 as true and 0 as false.<\/p>\n\n\n\n
NOT BETWEEN<\/h3>\n\n\n\n
To negate the BETWEEN<\/code> operator, you use the NOT<\/code> operator:<\/p>\n\n\n
value NOT BETWEEN low AND high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NOT BETWEEN<\/code> operator returns 1 if:<\/p>\n\n\n
value < low OR value > high<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Otherwise, it returns 0.<\/p>\n\n\n\n
For example, the following statement returns 0 because 15 is not between 10 and 20 is not true:<\/p>\n\n\n
SELECT<\/span> 15<\/span> NOT<\/span> BETWEEN<\/span> 10<\/span> AND<\/span> 20<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL BETWEEN operator examples<\/h2>\n\n\n\n
Let’s practice with some examples of using the BETWEEN<\/code> operator.<\/p>\n\n\n\n
1) Using MySQL BETWEEN with Numbers<\/h3>\n\n\n\n
See the following products<\/code> table in the sample database<\/a>:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example uses the BETWEEN<\/code> operator to find products whose buy prices between 90<\/code> and 100<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    productCode, \n    productName, \n    buyPrice\nFROM<\/span>\n    products\nWHERE<\/span>\n    buyPrice BETWEEN<\/span> 90<\/span> AND<\/span> 100<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
This query uses the greater than or equal (>=<\/code>) and less than or equal to ( <=<\/code> ) operators instead of the BETWEEN<\/code> operator to get the same result:<\/p>\n\n\n
SELECT<\/span> \n    productCode, \n    productName, \n    buyPrice\nFROM<\/span>\n    products\nWHERE<\/span>\n    buyPrice >= 90<\/span> AND<\/span> buyPrice <= 100<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To find the products whose buy prices are not between $20 and $100, you use the NOT BETWEEN<\/code> operator as follows:<\/p>\n\n\n
SELECT<\/span> \n    productCode, \n    productName, \n    buyPrice\nFROM<\/span>\n    products\nWHERE<\/span>\n    buyPrice NOT<\/span> BETWEEN<\/span> 20<\/span> AND<\/span> 100<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
You can rewrite the query above using the less than (<<\/code>), greater than (><\/code>), and the logical operator (AND<\/a><\/code>) like this:<\/p>\n\n\n
SELECT<\/span> \n    productCode, \n    productName, \n    buyPrice\nFROM<\/span>\n    products\nWHERE<\/span>\n    buyPrice < 20<\/span> OR<\/span> buyPrice > 100<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using MySQL BETWEEN operator with dates example<\/h3>\n\n\n\n
See the following orders table:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To check if a value is between a date range, you should explicitly cast<\/a> the value to the DATE<\/a> type. <\/p>\n\n\n\n
For example, the following statement returns the orders with the required dates between 01\/01\/2003 to 01\/31\/2003:<\/p>\n\n\n
SELECT<\/span> \n   orderNumber,\n   requiredDate,\n   status<\/span>\nFROM<\/span> \n   orders\nWHERE<\/span> \n   requireddate BETWEEN<\/span> \n     CAST<\/span>('2003-01-01'<\/span> AS<\/span> DATE<\/span>) AND<\/span> \n     CAST<\/span>('2003-01-31'<\/span> AS<\/span> DATE<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, we use the CAST()<\/code> to cast the literal string '2003-01-01'<\/code> into a DATE<\/code> value:<\/p>\n\n\n
CAST('2003-01-01' AS DATE)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the MySQL BETWEEN<\/code> operator to test if a value falls within a range of values.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
In practice, you’ll use the BETWEEN<\/code> operator in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code>, UPDATE<\/a><\/code>, and DELETE<\/a><\/code> statements.<\/p>\n\n\n\n