While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.
The SQL BETWEEN operator is used along with WHERE clause for providing a range of values. The values can be the numeric value, text value, and date. SQL BETWEEN operator is almost like SQL IN operators used in a sequential manner. The values are defined as part of the BETWEEN range are inclusive i.e. the values that are mentioned in the range are included at the start and end values. Let’s discuss in detail about the BETWEEN operator. As mentioned above BETWEEN operator can be used along with numeric value, text value, and date. We will discuss all the three in detail below.
SELECT Column(s) FROM table_name WHERE column BETWEEN value1 AND value2;
Using the above-mentioned syntax, we can define values as part of BETWEEN operator. Also, the syntax mentioned above remains the same for usage with a numeric value, text value, and date value.
We will understand the above-mentioned syntax in more detail through some examples for numeric value. Let’s consider the following Student table for example purpose.
I am using MySQL database and here is the script to create and insert example records in the Student table.
CREATE TABLE `Student` ( `rollno` int(11) unsigned NOT NULL, `studentname` varchar(20) DEFAULT NULL, `studentgender` varchar(5) DEFAULT NULL, `studentage` int(3) DEFAULT NULL, `studentpercent` int(3) DEFAULT NULL, `admissiondate` date DEFAULT NULL, PRIMARY KEY (`rollno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `Student` (`rollno`, `studentname`, `studentgender`, `studentage`, `studentpercent`, `admissiondate`) VALUES (1, 'George', 'M', 14, 85, '2018-01-01'), (2, 'Monica', 'F', 12, 88, '2018-01-31'), (3, 'Jessica', 'F', 13, 84, '2018-01-15'), (4, 'Tom', 'M', 11, 78, '2017-12-15');
Scenario: Get the percentage of students whose age is between 11 and 13.
SELECT StudentPercent FROM Student WHERE StudentAge BETWEEN 11 AND 13;
The SQL NOT BETWEEN operator is used for getting the values as part of result set which is outside of the range specified by the BETWEEN operator. Scenario: Get the percentage of students whose age is not between 11 and 13.
SELECT StudentPercent FROM Student WHERE StudentAge NOT BETWEEN 11 AND 13;
Scenario: Get the RollNo, StudentName and StudentAge where StudentName is between George and Jessica.
SELECT RollNo, StudentName, StudentAge FROM Student WHERE StudentName BETWEEN 'George' AND 'Jessica';
Scenario: Get the RollNo, StudentName and StudentAge where StudentName is not between George and Jessica.
SELECT RollNo, StudentName, StudentAge FROM Student WHERE StudentName NOT BETWEEN 'George' AND 'Jessica';
Scenario: Get the age of students whose admission is between 1st Jan 2018 and 31st Jan 2018.
SELECT StudentAge FROM Student WHERE admissiondate BETWEEN str_to_date('2018-01-01', '%Y-%m-%d') AND '2018-01-31';
Note that I am using MySQL native function
str_to_date to convert string to date. If the string is in default format, we can use it as-is too, just as I have used for the second argument. If you are using Oracle DB, then corresponding function is
Scenario: Get the age of students whose admission is not between 1st Jan 2018 and 31st Jan 2018.
SELECT StudentAge FROM Student WHERE admissiondate NOT BETWEEN str_to_date('2018-01-01', '%Y-%m-%d') AND '2018-01-31';
We can use multiple between operators too. Its syntax is:
SELECT Column(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 AND column_name BETWEEN value3 and value4 ... AND BETWEEN column_name BETWEEN valueN and valueM;
Using the above-mentioned syntax, we can use multiple BETWEEN operators. Scenario: Get the student name with age between 10 and 13 and marks between 80 to 85 percentage.
SELECT StudentName FROM Student WHERE StudentAge BETWEEN 10 AND 13 AND StudentPercent BETWEEN 80 AND 85;
If you’ve enjoyed this tutorial and our broader community, consider checking out our DigitalOcean products which can also help you achieve your development goals.