{"id":181,"date":"2019-10-05T11:18:26","date_gmt":"2019-10-05T04:18:26","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=181"},"modified":"2020-04-11T23:24:10","modified_gmt":"2020-04-11T16:24:10","slug":"mariadb-between","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-between\/","title":{"rendered":"MariaDB Between"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB between<\/code> operator to test if a value is between two other values.<\/p>\n\n\n\n

Introduction to MariaDB between operator<\/h2>\n\n\n\n

The MariaDB between<\/code> operator is a logical operator that returns true<\/code> if a value is between two values<\/p>\n\n\n\n

The between<\/code> operator is typically used in the where<\/a><\/code> clause to form a search condition.<\/p>\n\n\n\n

Here is the syntax of the between<\/code> operator:<\/p>\n\n\n

expression between low and high\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, low<\/code> and high<\/code> are literal values or expressions. The low<\/code> value must be less than the high<\/code> value.<\/p>\n\n\n\n
The between<\/code> operator returns true<\/code> if the expression<\/code> is less than or equal to the high<\/code> value and greater than or equal to the low<\/code> value.<\/p>\n\n\n\n
In other words, the between<\/code> operator is syntactic sugar of the following expression:<\/p>\n\n\n
expression >= low and expression <= high\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To negate the between<\/code> operator, you use the not<\/code> operator.<\/p>\n\n\n
expression not between low and high\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The not between<\/code> returns true<\/code> if the expression<\/code> is not between two other values, or:<\/p>\n\n\n
expression < low or expression > high\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MariaDB between operator examples<\/h2>\n\n\n\n
We’ll use the countries<\/code> table form the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
A) Using the MariaDB between operator with numbers example<\/h3>\n\n\n\n
The following example uses the between<\/code> operator to find countries that have areas between 1,566,500<\/code> and 2,780,400<\/code>:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    area \nfrom<\/span> \n    countries\nwhere<\/span> \n    area between<\/span> 1566500<\/span> \n        and<\/span> 2780400<\/span>\norder<\/span> by<\/span> \n    area;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
Note that the above query is equivalent to the following query:<\/p>\n\n\n
 select<\/span> \n    name<\/span>, \n    area \nfrom<\/span> \n    countries\nwhere<\/span> \n    area >= 1566500<\/span> and<\/span>\n    area <= 2780400<\/span>\norder<\/span> by<\/span> \n    area;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
B) Using MariaDB between operator with dates example<\/h3>\n\n\n\n
The following statement uses the between<\/code> operator to find countries which have national days that fall between August 17, 1945<\/code> and November 09, 1953<\/code>:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    national_day \nfrom<\/span> \n    countries\nwhere<\/span> \n    national_day \n        between<\/span> '1945-08-17'<\/span> \n            and<\/span> '1953-11-09'<\/span>\norder<\/span> by<\/span> \n    national_day;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/h3>\n\n\n\n
C) Using MariaDB not between operator example<\/h3>\n\n\n\n
The following statement uses the not between<\/code> operator to find countries whose national does not fall between August 17, 1945<\/code> and November 09, 1953<\/code>:<\/p>\n\n\n
select<\/span> \n    name<\/span>, \n    national_day \nfrom<\/span> \n    countries\nwhere<\/span> \n    national_day \n        not<\/span> between<\/span> '1945-08-17'<\/span> \n            and<\/span>  '1953-11-09'<\/span>\norder<\/span> by<\/span> \n    national_day;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"mariadb<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the MariaDB between<\/code> operator to test if a value is between two other values.<\/p>\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