{"id":180,"date":"2019-03-27T00:30:12","date_gmt":"2019-03-27T08:30:12","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=180"},"modified":"2020-04-11T06:59:56","modified_gmt":"2020-04-11T14:59:56","slug":"db2-between","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-between\/","title":{"rendered":"Db2 BETWEEN"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to the Db2 BETWEEN<\/code> operator to check whether a value lies between two other values.<\/p>\n\n\n\n

Introduction to Db2 BETWEEN operator<\/h2>\n\n\n\n

The Db2 BETWEEN<\/code> operator is a logical operator that determines whether a value lies between two values that are specified in ascending order. The BETWEEN<\/code> operator is often used in the WHERE<\/a><\/code> clause of the SELECT<\/a><\/code> statement to form the search condition for the rows returned by a query.<\/p>\n\n\n\n

The following shows 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> can be literal values or expressions. The BETWEEN<\/code> operator returns true if the value of the expression<\/code> lies between the low and high values, or simply:<\/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
The NOT BETWEEN<\/code> negates the BETWEEN<\/code> operator. It returns true if the value is not between two other values:<\/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
It is equivalent to the following expression:<\/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
Db2 BETWEEN operator examples<\/h2>\n\n\n\n
We will use the books<\/code> table from the sample database<\/a> to demonstrate the BETWEEN<\/code> operator:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Using Db2 BETWEEN operator with numbers example<\/h3>\n\n\n\n
This example uses the BETWEEN<\/code> operator to find books that have the number of pages between 100 and 125:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages\nFROM<\/span>    \n    books\nWHERE<\/span> \n    total_pages BETWEEN<\/span> 100<\/span> AND<\/span> 125<\/span>\nORDER<\/span> BY<\/span> \n    total_pages;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result set:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
It is equivalent to the following query:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    total_pages\nFROM<\/span>    \n    books\nWHERE<\/span> \n    total_pages >= 100<\/span> and<\/span> total_pages <= 125<\/span>\nORDER<\/span> BY<\/span> \n    total_pages;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using Db2 BETWEEN operator with dates example<\/h3>\n\n\n\n
The following example uses the BETWEEN<\/code> operator finds books whose published dates are between Jan 01, 2017 and June 30, 2017:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    published_date\nFROM<\/span>    \n    books\nWHERE<\/span> \npublished_date BETWEEN<\/span> '2017-01-01'<\/span> AND<\/span> '2017-06-30'<\/span>\nORDER<\/span> BY<\/span> \n    published_date;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"Db2<\/figure>\n\n\n\n
3) Db2 NOT BETWEEN operator example<\/h3>\n\n\n\n
This example uses the NOT BETWEEN<\/code> operator to find books whose ratings are not between 3 and 4.5:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    rating\nFROM<\/span>    \n    books\nWHERE<\/span> rating NOT<\/span> BETWEEN<\/span> 3<\/span> AND<\/span> 4.5<\/span>\nORDER<\/span> BY<\/span> \n    rating;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"Db2<\/figure>\n\n\n\n
The query above is equivalent to the following query that uses comparison operators with the logical operator OR<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    title, \n    rating\nFROM<\/span>    \n    books\nWHERE<\/span> rating < 3<\/span> OR<\/span> rating > 4.5<\/span>\nORDER<\/span> BY<\/span> \n    rating;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the Db2 BETWEEN<\/code> operator to determine whether a value lies between two 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